user1472150 user1472150 - 1 month ago 6
SQL Question

How to get sum by MonthYear in sql?

I have a table like this

id Date Amount

1 2016-09-29 09:25:37.000 25.13
2 2016-08-01 17:20:39.000 598.00
3 2016-09-29 09:24:47.000 15.60
4 2016-07-28 17:50:11.000 61.80
5 2016-07-28 17:53:56.000 31.40
6 2016-07-22 10:40:27.000 74.16


I'm trying to get two columns like this,

MonthYear Total

Sep 2016 40.73
Aug 2016 598.00
Jul 2016 167.36


But, I want to get most recent year and month to top.

Answer

Try this sql,

  SELECT CONVERT(CHAR(4), Date, 100) + CONVERT(CHAR(4), Date, 120) 
      AS MonthYear, SUM(Amount) 
      AS Total,
          CAST(CONVERT(varchar(4), Date, 120) AS int) 
      AS Year, DATEPART(m, Date) As Month
    FROM your_table 
GROUP BY CONVERT(CHAR(4), Date, 100) + CONVERT(CHAR(4), Date,120),CAST(CONVERT(varchar(4), Date, 120) AS int), DATEPART(m, Date)
ORDER BY Year DESC, Month DESC
Comments