How to ignore (null or 0) values in subquery-CodePudding

I have this table named (sale):

category	amount	currency
cat1	10,000	USD
cat2	5,000	ERU
cat3	15,000	SAR
cat2	30,000	USD
cat1	45,000	SAR
cat3	7,000	ERU
cat2	3,000	ERU
cat4	0	USD

I wrote this query to get the results in the table below: note: the query has subqueries from the same table:

SELECT  S.[Category]

       ,(SELECT ISNULL(SUM([Amount]),0)
           FROM SALE
          WHERE [Currency] = 'USD'
            AND SALE.[CategoryID] = S.[CategoryID]) AS 'Total_USD'
            
       ,(SELECT ISNULL(SUM([Amount]),0) 
           FROM SALE
          WHERE [Currency] = 'ERU'
            AND SALE.[CategoryID] = S.[CategoryID]) AS 'Total_ERU'
            
       ,(SELECT ISNULL(SUM([Amount]),0) 
           FROM SALE
          WHERE [Currency] = 'SAR'
            AND SALE.[CategoryID] = s.[CategoryID]) AS 'Total_SAR'
        
        FROM Sale AS S
        GROUP BY S.[CategoryID],S.[CategoryName]

results:

category	Total USD	Total ERU	Total SAR
cat1	10,000	0	45,000
cat2	30,000	8,000	0
cat3	0	7,000	15,000
cat4	0	0	0

My question is: How to avoid (0) values if all subqueries return (null or zeros) value, like above in (cat4) .

I tried (Having) clause, but I have to write all subqueries again in (Having), which is not a good thing to do.

CodePudding user response：

If you switch to conditional aggregation, which would be way more performant anyway, then you can use a HAVING. As you want to exclude the row with 'ERU' you need to use a CASE expression in it:

SELECT S.Category,
       SUM(CASE currency WHEN 'USD' THEN amount ELSE 0 END) AS USD,
       --SUM(CASE currency WHEN 'EUR' THEN amount ELSE 0 END) AS EUR, --Seems you don't want EUR rows
       SUM(CASE currency WHEN 'SAR' THEN amount ELSE 0 END) AS SAR,
       SUM(CASE currency WHEN 'ERU' THEN amount ELSE 0 END) AS ERU
FROM dbo.Sale S
GROUP BY S.Category
HAVING SUM(CASE WHEN currency IN ('USD','SAR','ERU') THEN amount END) != 0;