SQL grouping by month and year
SqlSql ServerSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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)
Solution 2 - Sql
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)
Solution 3 - Sql
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')
Solution 4 - Sql
Yet another alternative:
Select FORMAT(date,'MM.yy')
...
...
group by FORMAT(date,'MM.yy')
Solution 5 - Sql
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
Solution 6 - Sql
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)
Solution 7 - Sql
For mariaDB you can:
SELECT DATE_FORMAT(date, '%m-%Y')
FROM [Order]
GROUP BY
DATE_FORMAT(date, '%m-%Y')
Solution 8 - Sql
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?
Solution 9 - Sql
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!