CODEWITHSUNDEEP
sqlsql-servert-sql
Morten Lau Torp Nielsen
Morten Lau Torp Nielsen

Reputation: 45

Include 2 SUM functions with 2 where conditions

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

Upvotes: 1

Views: 57

Answers (4)

Radim Bača
Radim Bača

Reputation: 10701

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.

Upvotes: 0

gotqn
gotqn

Reputation: 43636

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

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

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

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 1

Related Questions