Include 2 SUM functions with 2 where conditions

Question

How do I go about getting two different where conditions in my SELECT statement, so I can get the SUM grouped by month?

SELECT
[tpdb].[tLedgerEntry].CalendarMonth AS Måned,
(select SUM(Amount) FROM [tpdb].[tLedgerEntry] where CompanyId = 'Nordlux A/S' and CalendarYear = 2018 and LedgerAccount>=6001 and LedgerAccount <= 6090) AS Varelager,
(select SUM(Amount) FROM [tpdb].[tLedgerEntry] where CompanyId = 'Nordlux A/S' and CalendarYear = 2018 and LedgerAccount>=6100 and LedgerAccount <= 6156) AS Debitorer
FROM [tpdb].[tLedgerEntry]
GROUP BY [tpdb].[tLedgerEntry].CalendarMonth

This is the result I'm getting now which is wrong.

NULL    32582633.07 13901648.50
1   32582633.07 13901648.50
2   32582633.07 13901648.50
3   32582633.07 13901648.50
4   32582633.07 13901648.50
5   32582633.07 13901648.50
6   32582633.07 13901648.50
7   32582633.07 13901648.50
8   32582633.07 13901648.50
9   32582633.07 13901648.50
10  32582633.07 13901648.50
11  32582633.07 13901648.50
12  32582633.07 13901648.50

Either correlated sub-queries, or GROUP BY - not both. – jarlh Jan 15 '19 at 10:07 — jarlh, Jan 15 '19 at 10:07

score 2 · Accepted Answer · answered Jan 15 '19 at 10:10

Try this:

SELECT
[tpdb].[tLedgerEntry].CalendarMonth AS Måned,
SUM(IIF(LedgerAccount>=6001 and LedgerAccount <= 6090, Amount, 0))  AS Varelager,
SUM(IIF(LedgerAccount>=6100 and LedgerAccount <= 6156, Amount, 0))  AS Debitorer
FROM [tpdb].[tLedgerEntry]
 where CompanyId = 'Nordlux A/S' and CalendarYear = 2018
GROUP BY [tpdb].[tLedgerEntry].CalendarMonth

score 1 · Answer 2 · answered Jan 15 '19 at 10:08

Try using case when as conditional aggregation

SELECT
[tpdb].[tLedgerEntry].CalendarMonth AS Måned,
SUM(case when CompanyId = 'Nordlux A/S' and CalendarYear = 2018 and LedgerAccount>=6001 and LedgerAccount <= 6090 then Amount end) AS Varelager,
SUM(case when CompanyId = 'Nordlux A/S' and CalendarYear = 2018 and LedgerAccount>=6100 and LedgerAccount <= 6156 then Amount end) AS Debitorer
FROM [tpdb].[tLedgerEntry]
GROUP BY [tpdb].[tLedgerEntry].CalendarMonth

score 0 · Answer 3 · answered Jan 15 '19 at 10:08

use case when

 select [tpdb].[tLedgerEntry].CalendarMonth AS Måned,
                         sum(case when CompanyId = 'Nordlux A/S' 
                              and CalendarYear = 2018
                              and LedgerAccount>=6001 
                              and LedgerAccount <= 6090 
                              then Amount else 0 end) Varelager,
                        sum(case when CompanyId = 'Nordlux A/S' 
                             and CalendarYear = 2018 
                             and LedgerAccount>=6100 
                             and LedgerAccount <= 6156 
                             then amount else 0 end) Debitorer
    from [tpdb].[tLedgerEntry]
    group by [tpdb].[tLedgerEntry].CalendarMonth

score 0 · Answer 4 · answered Jan 15 '19 at 10:13

Use correlated subquery.

SELECT
    t1.CalendarMonth AS Måned,
    (
      select SUM(Amount) 
      FROM [tpdb].[tLedgerEntry] t2
      where t2.CompanyId = 'Nordlux A/S' and 
            t2.CalendarYear = 2018 and 
            t2.LedgerAccount>=6001 and 
            t2.LedgerAccount <= 6090 and
            t2.CalendarMonth = t1.CalendarMonth
    ) AS Varelager,
    (
      select SUM(Amount) 
      FROM [tpdb].[tLedgerEntry] t2
      where t2.CompanyId = 'Nordlux A/S' and 
            t2.CalendarYear = 2018 and 
            t2.LedgerAccount>=6100 and 
            t2.LedgerAccount <= 6156 and
            t2.CalendarMonth = t1.CalendarMonth
    ) AS Debitorer, 
FROM (
   SELECT distinct CalendarMonth  FROM [tpdb].[tLedgerEntry]
) t1

If you have indexes on CalendarMonth, CalendarYear, LedgerAccount and the selectivity is high, then the correlated subquery may have better performance.

Moreover, pushing the distinct into a subquery may be beneficial for your query as well.

Include 2 SUM functions with 2 where conditions

4 Answers4