Avoid NULL value in CASE SQL SERVER

Question

How to avoid NULL values in CASE and SUM by P.Id. Problem is that i have more than one DPB.ProductTypeId in DPB table

SELECT  P.[Id], 
        CASE 
        WHEN DPB.ProductTypeId = 1 THEN SUM(DPB.BonusAmount)
        END AS [CasinoBonus]
FROM Player P           
JOIN PlayerBonus DPB ON P.[Id] = DPB.[PlayerId]
group by P.[Id],DPB.ProductTypeId

add some sample data and your expected output. and also add what issue you are facing. — mkRabbani
– mkRabbani, Commented Jul 19, 2019 at 9:16
If i put sum i got two rows, one with summarized values and one NULL — user2171512
– user2171512, Commented Jul 19, 2019 at 9:20

Zaynul Abadin Tuhin · Accepted Answer · 2019-07-19 09:22:34Z

1

use case when inside sum

 SELECT  P.[Id], 
            sum(CASE 
            WHEN DPB.ProductTypeId = 1 THEN DPB.BonusAmount
            else 0
            END) AS [CasinoBonus]
    FROM Player P           
    JOIN PlayerBonus DPB ON P.[Id] = DPB.[PlayerId]
    where P.[Id] is not null and DPB.[PlayerId] is not null
    group by P.[Id],DPB.ProductTypeId

edited Jul 19, 2019 at 9:22

answered Jul 19, 2019 at 9:20

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

Your CASE expression might need an ELSE 0 case.

Gordon Linoff · Accepted Answer · 2019-07-19 11:06:50Z

The case should be the argument to the sum(). You query should look like this:

SELECT P.[Id], 
       SUM(CASE WHEN DPB.ProductTypeId = 1 THEN DPB.BonusAmount
           END) AS [CasinoBonus]
FROM Player P JOIN
     PlayerBonus DPB
     ON P.[Id] = DPB.[PlayerId]
GROUP BY P.[Id];

Note that you don't want DPB.ProductTypeId in the GROUP BY.

That said, you may simply want:

SELECT P.[Id], 
       SUM(DPB.BonusAmount) AS [CasinoBonus]
FROM Player P LEFT JOIN
     PlayerBonus DPB
     ON P.[Id] = DPB.[PlayerId] AND 
        DPB.ProductTypeId = 1
GROUP BY P.[Id];

Moving the condition to the WHERE clause removes the need for the CASE entirely. The LEFT JOIN keeps all players, even those that don't have that product type.

Collectives™ on Stack Overflow

Avoid NULL value in CASE SQL SERVER

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related