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
x
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