Skip to content
Advertisement

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

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement