How to group by month using SQL Server?

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 PaymentsGROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)ORDER BY [Month]

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';SELECTItemID,UserID,DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0) [Month],IsPaid,SUM(Amount)FROM LIVE LINNER JOIN Payments I ON I.LiveID = L.RECORD_KEYWHERE 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.

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

ALTER TABLE dbo.Payments ADD PaymentMonth AS MONTH(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,(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 1 AND UserID = 100) AS 'Jan',(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 2 AND UserID = 100) AS 'Feb',.... and so on .....FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY WHERE UserID = 16178 

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.

Another approach, that doesn't involve adding columns to the result, is to simply zero-out the day component of the date, so 2016-07-13 and 2016-07-16 would both be 2016-07-01 - thus making them equal by month.

If you have a date (not a datetime) value, then you can zero it directly:

SELECTDATEADD( day, 1 - DATEPART( day, [Date] ), [Date] ),COUNT(*)FROM[Table]GROUP BYDATEADD( day, 1 - DATEPART( day, [Date] ), [Date] )

If you have datetime values, you'll need to use CONVERT to remove the time-of-day portion:

SELECTDATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) ),COUNT(*)FROM[Table]GROUP BYDATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) )

Now your query is explicitly looking at only payments for year = 2010, however, I think you meant to have your Jan/Feb/Mar actually represent 2009. If so, you'll need to adjust this a bit for that case. Don't keep requerying the sum values for every column, just the condition of the date difference in months. Put the rest in the WHERE clause.

SELECT SUM( case when DateDiff(m, PaymentDate, @start) = 0 then Amount else 0 end ) AS "Apr",SUM( case when DateDiff(m, PaymentDate, @start) = 1 then Amount else 0 end ) AS "May",SUM( case when DateDiff(m, PaymentDate, @start) = 2 then Amount else 0 end ) AS "June",SUM( case when DateDiff(m, PaymentDate, @start) = 3 then Amount else 0 end ) AS "July",SUM( case when DateDiff(m, PaymentDate, @start) = 4 then Amount else 0 end ) AS "Aug",SUM( case when DateDiff(m, PaymentDate, @start) = 5 then Amount else 0 end ) AS "Sep",SUM( case when DateDiff(m, PaymentDate, @start) = 6 then Amount else 0 end ) AS "Oct",SUM( case when DateDiff(m, PaymentDate, @start) = 7 then Amount else 0 end ) AS "Nov",SUM( case when DateDiff(m, PaymentDate, @start) = 8 then Amount else 0 end ) AS "Dec",SUM( case when DateDiff(m, PaymentDate, @start) = 9 then Amount else 0 end ) AS "Jan",SUM( case when DateDiff(m, PaymentDate, @start) = 10 then Amount else 0 end ) AS "Feb",SUM( case when DateDiff(m, PaymentDate, @start) = 11 then Amount else 0 end ) AS "Mar"FROM Payments IJOIN Live Lon I.LiveID = L.Record_KeyWHERE Year = 2010 AND UserID = 100