How to group by month using SQL Server?
Solution 1:
SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)
ORDER BY [Month]
You could also try:
SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDER BY Year, Month
Solution 2:
Restrict the dimension of the NVARCHAR to 7, supplied to CONVERT to show only "YYYY-MM"
SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)
ORDER BY [Month]
Solution 3:
I prefer combining DATEADD
and DATEDIFF
functions like this:
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0)
Together, these two functions zero-out the date component smaller than the specified datepart (i.e. MONTH
in this example).
You can change the datepart bit to YEAR
, WEEK
, DAY
, etc... which is super handy.
Your original SQL query would then look something like this (I can't test it as I don't have your data set, but it should put you on the right track).
DECLARE @start [datetime] = '2010-04-01';
SELECT
ItemID,
UserID,
DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0) [Month],
IsPaid,
SUM(Amount)
FROM LIVE L
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
AND PaymentDate > @start
One more thing: the Month
column is typed as a DateTime
which is also a nice advantage if you need to further process that data or map it .NET object for example.