How to sum total amount for every month in a year?
sql, sql-server
Solution
I think you just want an aggregation:
select id, month(date) as "month", sum(amount) as TotalAmount
from t
where year(date) = 2013
group by id, month(date)
Problem
I have a database in SQL Server 2012 and there is a table with dates in `D.M.YYYY` format like below: ``` ID | Date(date type) | Amount(Numeric) 1 3.4.2013 16.00 1 12.4.2013 13.00 1 2.5.2013 9.50 1 18.5.2013 10.00 ``` I need to sum the total amount for every month in a given year. For example: ``` ID | Month | TotalAmount 1 1 0.00 ... 1 4 29.00 1 5 19.50 ``` I thought what I needed was to determine the number of days in a month, so I created a function which is described in determine the number of days, and it worked. After that I tried to compare two dates(date type) and got stuck; there are some examples out there, but all of them about datetime. Is this wrong? How can I accomplish this?