Have this MS SQL query
SELECT COUNT(*) AS Total, CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.AmountGB, 0)) END AS AmountGB, CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.TaxAmountGB, 0)) END AS TaxAmountGB, CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.AmountUS, 0)) END AS AmountUS, CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.TaxAmountUS, 0)) END AS TaxAmountUS, CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.AmountAU, 0)) END AS AmountAU, CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.TaxAmountAU, 0)) END AS TaxAmountAU, ... ... ... FROM Page AS p RIGHT JOIN (SELECT PageId,... FROM Campaign WHERE CampaignId = @Campaign AND...) AS c ON p.PageId = c.PageId
When there is no rows matching on the left side of the JOIN want to return 0s instead of NULLs that is why have
WHEN COUNT(*) = 0 THEN 0 ELSE...
Without having to define a scalar function, is there way to remove the repetitive
WHEN COUNT(*) = 0 THEN 0 ELSE...
or a more concise way to return 0’s?
NOTE: Values in Page table (ex. AmountGB) can have nulls that is why the ISNULL(f.AmountGB, 0).
NOTE: Not allowed to change the table definition.
Advertisement
Answer
This would be much simpler as:
SELECT COUNT(*) AS Total, COALESCE(SUM(f.AmountGB), 0)) AS AmountGB, COALESCE(SUM(f.TaxAmountGB), 0)) AS TaxAmountGB, . . .
I also recommend LEFT JOIN
:
SELECT COUNT(*) AS Total, COALESCE(SUM(f.AmountGB), 0)) AS AmountGB, COALESCE(SUM(f.TaxAmountGB), 0)) AS TaxAmountGB, . . . FROM Campaign c LEFT JOIN PageTotals f ON f.PageId = p.PageId WHERE CampaignId = @Campaign AND ...