GROUP BY Issue - Grouping per month

0
votes
SELECT 
   Sum(Earning) AS Amount, 
   MONTH(DateCreated) AS Month 
FROM 
   TheTable 
WHERE 
   DateCreated 
BETWEEN 
   @DateFrom AND @DateTo 
GROUP BY **???**

I want to display Earning per month and it should not group the month if it appears next year again e.g.

May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, June etc.

Meaning if the year differs it should continue the list without grouping e.g. May 2015 and May 2016 altogether.

Thank you

2
sqlsql-serversql-server-2008
MySQL or SQL Server? - 1000111
I removed the MySQL tag based on preponderance of evidence. - Gordon Linoff
You should group by Month and Earning.. - Maverick
You want to group by year and month but only output month? - P.Salmon

2 Answers

2
votes

Include the year in the select and group by:

SELECT YEAR(DateCreated) as Year, MONTH(DateCreated) AS Month, 
       Sum(Earning) AS Amount
FROM TheTable 
WHERE DateCreated BETWEEN @DateFrom AND @DateTo 
GROUP BY YEAR(DateCreated), MONTH(DateCreated)
ORDER BY YEAR(DateCreated), MONTH(DateCreated);
1
votes

You can use function for group by month() 

SELECT 
 Sum(Earning) AS Amount, 
 MONTH(DateCreated) AS Month 
FROM 
  TheTable 
WHERE 
  DateCreated 
BETWEEN 
   @DateFrom AND @DateTo 
GROUP BY  MONTH(DateCreated)