SQL syntax for monthly and yearly totals.

[opticalman]

I have table of amounts paid during the day. The following will give me a list of daily totals

select date , sum(paid)
from ledger
group by date
order by date

Is there a way I can get a list of Monthly or Annual totals?

Thanks in advance.

Jim Rumbaugh

 

[rac2]

See Books Online topics DATEPART and WITH ROLLUP.

Also GROUP BY a DATETIME column only works if the time of day is the same for every date, as it might be if you are inserting and converting strings like '2007-01-25', these are converted to midnight, '2007-01-25 00:00:00' by SQL Server.

 

[opticalman]

Thanks Rac2

I did what you said. I now have working code that displays the totals of each month per each year

SELECT DATEPART(year, date) AS Expr1,
DATEPART(month, date) AS Expr2,
SUM(paid) AS Expr3
FROM ledger
GROUP BY DATEPART(year, date), DATEPART(month, date)
ORDER BY expr1, expr2

I consider this problem solved

Jim Rumbaugh