How do I sum up each month for all 12 months in one query

Question

The query I work with works with YEAR and MONTH to get one SUM for each date:

EKA = CONVERT(DECIMAL(10,0),
    (SELECT ISNULL(SUM(LP.QTY), 0) 
     FROM Invoice LP
     WHERE LP.ARTICLE = A.ARTICLE
       AND YEAR(LP.DATE) = YEAR(GETDATE()) 
       AND MONTH(LP.date) = 1/2/3/4/5/6/7/8/9/10/11/12)
FROM ARTICLE A
WHERE A.ARTICLE = 'A001'

I would like to change it so it will give me a SUM for each month, as a separate row, without having to hardcode the MONTH in the query:

MONTH	EKA
1	100
2	110
3	90
...	...
11	40
12	150

Answer 1

You can group by EOMONTH() to get, efficiently, what you need. This approach scales up cleanly to handle multiple years' worth of data if you need it to.

SELECT ISNULL(SUM(LP.QTY), 0) articles_sold,
       LP.ARTICLE,
       EOMONTH(LP.DATE) month_ending
  FROM Invoice LP
  JOIN ARTICLE A ON LP.ARTICLE = A.ARTICLE
 WHERE LP.DATE >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
   AND LP.DATE < DATEFROMPARTS(YEAR(GETDATE()+1), 1, 1)
   AND A.ARTICLE = 'whatever'
 GROUP BY LP.ARTICLE, EOMONTH(LP.DATE)

If you have an index on LP(ARTICLE, DATE) this query will exploit it.