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.

4

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) 

1

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)

3

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')

1

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

1

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

1

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)

1

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!

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy