How To Group By Month Using Sql Server?

January 13, 2024 Post a Comment

I have a table which has this schema ItemID UserID Year IsPaid PaymentDate Amount 1 1 2009 0 2009-11-01 300 2 1 2009 0

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
GROUPBY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDERBYYear, 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:

GROUPBY 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
INNERJOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID =16178AND 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.

Solution 4:

If you need to do this frequently, I would probably add a computed column PaymentMonth to the table:

ALTERTABLE dbo.Payments ADD PaymentMonth ASMONTH(PaymentDate) PERSISTED

It's persisted and stored in the table - so there's really no performance overhead querying it. It's a 4 byte INT value - so the space overhead is minimal, too.

Once you have that, you could simplify your query to be something along the lines of:

SELECT ItemID, IsPaid,
(SELECTSUM(Amount) FROM Payments WHEREYear=2010And PaymentMonth =1AND UserID =100) AS'Jan',
(SELECTSUM(Amount) FROM Payments WHEREYear=2010And PaymentMonth =2AND UserID =100) AS'Feb',
.... and so on .....
FROM LIVE L 
INNERJOIN Payments I ON I.LiveID = L.RECORD_KEY 
WHERE UserID =16178

Solution 5:

DECLARE @start [datetime] = 2010/4/1;

Should be...

DECLARE @start [datetime] = '2010-04-01';

The one you have is dividing 2010 by 4, then by 1, then converting to a date. Which is the 57.5th day from 1900-01-01.

Try SELECT @start after your initialisation to check if this is correct.

Lab Sql