SQL Server Performance Forum – Threads Archive
problem in getting rows using group by
Hello, I need help with the following. I have a table with the following columns. Id Year Amount1 2004 100.00
2 2004 100.00
3 2006 300.00
4 2006 500.00 I need to prepare a sql statement that produces result like this: Year Amount
2006 800.00 (current year , sum of current year’s amount)
2005 0.00 (current year minus 1 , sum of current year minus 1 amount)
2004 200.00 (current year minus 2, sum of current year minus 2 amount) First row is the current year and sum of all amount for that year.
The second row is the current year minus 1 and sum of all amount for that year and so on. Thank you.
Oops, a small change in the output. I would like to retreive all in one row. For eg, Employee Year Amount Year Amount Year Amount
——————————————–
John Woo 2006 800.00 2005 0.00 2004 200.00
Tom Taren 2006 400.00 2005 40.00 2004 100.00
Jill Jacob 2006 200.00 2005 50.00 2004 500.00 I would like to retreive rows – how much an employee has spent for current year, for currentYear-1, currentYear-2. Thanks.
(For your first request) CREATE TABLE testTable(
[year] char(4),
amount decimal(5, 2)
) GO declare @years table ( y char(4) ) insert into @years
select 2004
union all
select 2005
union all
select 2006 select * from @years select yrs.y, sum( isnull( amount, 0 ) )
from @years yrs
left join testTable on testTable.[year] = yrs.y
group by yrs.y
For your other request, google Cross Tab Query for SQL Server, and you should find examples
Read about Cross-tab Reports in sql server help file Madhivanan Failing to plan is Planning to fail
]]>