Skip to content
Advertisement

Eliminate duplication from a SQL query to avoid returning NULLs from a LEFT/RIGHT JOIN

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