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

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

• sql
• sql-server
• sum

Share Improve this question Follow edited Mar 3 at 16:13 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Mar 3 at 14:01 Kevin ZornKevin Zorn 1333 bronze badges 7

• Well, group by month... – mr mcwolf Commented Mar 3 at 14:20
• Several product specific functions used above. Which dbms are you using? – jarlh Commented Mar 3 at 14:44
• Microsoft SQL Server – Kevin Zorn Commented Mar 3 at 14:54
• 1 Sample data for both tables would help immensely. – Charlieface Commented Mar 3 at 14:55
• 1 You'll get MUCH better performance by changing the WHERE clause to not need the YEAR() or MONTH() functions on the stored dates, and instead constructing a range from the first of this year to less than (not equal to) the first of next year. – Joel Coehoorn Commented Mar 3 at 16:18

 |  Show 2 more comments

5 Answers 5

Sorted by: Reset to default 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.

Convert your query to a join and then aggregate by month:

SELECT
    MONTH(LP.date),
    CONVERT(DECIMAL(10,0), ISNULL(SUM(LP.QTY), 0)) AS EKA
FROM Invoice LP
LEFT JOIN ARTICLE A
    ON A.ARTICLE = LP.ARTICLE AND
       A.ARTICLE = 'A001'
WHERE
    YEAR(LP.DATE) = YEAR(GETDATE()) -- current year only
GROUP BY
    MONTH(LP.date)
ORDER BY
    MONTH(LP.date);

Assuming LP.Date is a Date rather than a DateTime, and that there are no other columns needed from the Article table:

SELECT MONTH(LP.Date) Month, ISNULL(SUM(LP.QTY), 0) EKA
FROM ARTICLE A
LEFT JOIN Invoice LP ON LP.Article = A.Article
       AND LP.DATE >= DATEFROMPARTS(YEAR(getdate()),  1,1)
       AND LP.DATE <  DATEFROMPARTS(YEAR(GETDATE())+1,1,1)
WHERE A.ARTICLE = 'A001'
GROUP BY MONTH(LP.Date)

Note how I restructured the conditional checks on LP.DATE so we don't need to mutate the stored data. This can dramatically improve performance.

The other answers imply the existence of Article records in each month. If you want to see zeros for the months where nothing happened you'll need to join your table to a dense list of months. One way to do it is to create a Values table containing the months 1 through 12.

Select Year(Current_Date), D.Month, sum(lp.qty) as EKA
From (
    Values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) as D(Month)
  Left Outer Join Invoice lp On D.Month=month(lp.date)
Where lp.article='A001' and 
        lp.date between datefromparts(Year(Current_Date),1,1) 
                    and datefromparts(Year(Current_Date),12,31)
Group By 1,2

Note that the article table is superfluous here because first you joined on the article key and then you filtered it on the article key! Just filter on lp.article itself and save dragging that whole table in there.

Example

select YEAR(GETDATE()) yy,mon,Article
  ,CONVERT(DECIMAL(10,0),isnull(sumQty,0)) EKA
FROM (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))t(mon)
left join (
  SELECT min(lp.Article) Article, YEAR(LP.date) yy,MONTH(LP.date) mm
     ,SUM(LP.QTY) sumQty
  FROM Article a
  inner join Invoice LP on a.Article=lp.Article
  WHERE a.ARTICLE = 'A001'
    AND YEAR(LP.DATE) = YEAR(GETDATE()) 
  group by YEAR(LP.DATE),MONTH(LP.date)
 ) tot on tot.mm=t.mon

yy	mon	Article	EKA
2025	1	A001	10
2025	2	A001	10
2025	3	null	0
2025	4	A001	12
2025	5	A001	13
2025	6	null	0
2025	7	null	0
2025	8	null	0
2025	9	null	0
2025	10	null	0
2025	11	null	0
2025	12	null	0

fiddle

If we imagine that this query is called from the frontend, we can pass the year and the article as parameters.

declare @report_year int =year(getdate());
declare @Article varchar(20)='A001';

select @report_year as yy,M.mm,@Article Article
  ,CONVERT(DECIMAL(10,0),isnull(sumQty,0)) EKA
FROM (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))M(mm)
left join (
  SELECT min(lp.Article) Article, YEAR(LP.date) yy,MONTH(LP.date) mm
     ,SUM(LP.QTY) sumQty
  FROM Article a
  inner join Invoice LP on a.Article=lp.Article
  WHERE a.ARTICLE = @Article
    AND YEAR(LP.DATE) = @report_year
  group by YEAR(LP.DATE),MONTH(LP.date)
 ) tot  on tot.mm=M.mm

There will also be no "ugly" nulls in output. Demo