title: How to group by month using SQL Server?
body: <p>I have a table which has this schema</p>
  
  <pre><code>ItemID    UserID    Year    IsPaid    PaymentDate  Amount
  1         1         2009    0         2009-11-01  300
  2         1         2009    0         2009-12-01  342
  3         1         2010    0         2010-01-01  243
  4         1         2010    0         2010-02-01  2543
  5         1         2010    0         2010-03-01  475
  </code></pre>
  
  <p>I'm trying to get a query working which shows the totals for each month. So far I've tried DateDiff and nested selects, but neither gives me what I want. This is the closest I have I think:</p>
  
  <pre><code>DECLARE @start [datetime] = 2010/4/1;
  SELECT ItemID, IsPaid,
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And DateDiff(m, PaymentDate, @start) = 0 AND UserID = 100) AS "Apr",
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =1 AND UserID = 100) AS "May",
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =2 AND UserID = 100) AS "Jun", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =3 AND UserID = 100) AS "Jul", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =4  AND UserID = 100) AS "Aug", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =5  AND UserID = 100) AS "Sep", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =6  AND UserID = 100) AS "Oct", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =7 AND UserID = 100) AS "Nov", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =8 AND UserID = 100) AS "Dec", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =9 AND UserID = 100) AS "Jan", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =10 AND UserID = 100) AS "Feb", 
  (SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =11 AND UserID = 100) AS "Mar" 
  FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY 
  WHERE UserID = 16178 
  </code></pre>
  
  <p>But I just get nulls when I should be getting values. Am I missing something?</p>