How to sum total amount for every month in a year?

Question

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?

Answer 1

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)