Have this MS SQL query
x
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