CODEWITHSUNDEEP
sqlsql-serverselectcase
user2171512
user2171512

Reputation: 561

Avoid NULL value in CASE SQL SERVER

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

Upvotes: 0

Views: 1378

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 1

Related Questions