SQL grouping by month and year

I'm not sure what should I write in the following SQL query to show 'date' column like this: "month-year" - "9-2011".

SELECT MONTH(date) + '.' + YEAR(date) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada FROM [Order] WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3') GROUP BY MONTH(date), YEAR(date) 

So, what I want to do is to change the data from the first column to show month and year instead of showing month only.

9 Answers

SELECT CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada FROM [Order] WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3') GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) 

Or as @40-Love mentioned you can cast with leading zeroes:

GROUP BY CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2) 

I'm guessing this is MS SQL, as it looks like MS SQL syntax.

You should put the same thing in the group by as you have in the select.

For example:

Select MONTH(date)+'-'+YEAR(date), .... ... ... ... group by MONTH(date)+'-'+YEAR(date) 

SQL Server 2012 above, I prefer use format() function, more simplify.

SELECT format(date,'MM.yyyy') AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada FROM [Order] WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3') GROUP BY format(date,'MM.yyyy') 

Yet another alternative: Select FORMAT(date,'MM.yy') ... ... group by FORMAT(date,'MM.yy') 

You can try multiplication to adjust the year and month so they will be one number. This, from my tests, runs much faster than format(date,'yyyy.MM'). I prefer having the year before month for sorting purpose. Code created from MS SQL Server Express Version 12.0.

SELECT (YEAR(Date) * 100) + MONTH(Date) AS yyyyMM FROM [Order] ... GROUP BY (YEAR(Date) * 100) + MONTH(Date) ORDER BY yyyyMM 

If I understand correctly. In order to group your results as requested, your Group By clause needs to have the same expression as your select statement.

GROUP BY MONTH(date) + '.' + YEAR(date) 

To display the date as "month-date" format change the '.' to '-' The full syntax would be something like this.

SELECT MONTH(date) + '-' + YEAR(date) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada FROM [Order] WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3') GROUP BY MONTH(date) + '.' + YEAR(date) 

For mariaDB you can:

SELECT DATE_FORMAT(date, '%m-%Y') FROM [Order] GROUP BY DATE_FORMAT(date, '%m-%Y') 

Link:

In postgresql I can write a similar query with a date-format function (to_char) and grouping just by date:

SELECT to_char (datum, 'MM-YYYY') AS mjesec FROM test GROUP BY datum ORDER BY datum; 

Such thing is surely possible with SQL-Server too, isn't it?

If you want to stay having the field in datetime datatype, try using this:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, o.[date]), 0) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada FROM [Order] o WHERE (idCustomer = 1) AND (o.[date] BETWEEN '2001-11-3' AND '2011-11-3') GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.[date]), 0) 

It it also easy to change to group by hours, days, weeks, years...
I hope it is of use to someone,

Regards!