I have this table:
table name: sale
-----------------------------------------<br />
category | amount | currency |<br />
-----------------------------------------<br />
cat1 | 10,000 | USD |<br />
cat2 | 5,000 | EUR |<br />
cat3 | 15,000 | SAR |<br />
cat2 | 30,000 | USD |<br />
cat1 | 45,000 | SAR |<br />
cat3 | 7,000 | EUR |<br />
cat2 | 3,000 | EUR |
I want a query (sql server) that return this result:
-------------------------------------------<br />
category | Total_USD | Total_EUR | Total_SAR |<br />
-------------------------------------------<br />
cat1 | 10,000 | 0 | 45,000 |<br />
cat2 | 30,000 | 8,000 | 0 |<br />
cat3 | 0 | 7,000 | 15,000 |
What I have tried:
I have tried this:
SELECT S.[CategoryID]
,S.[CategoryName]
,(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]
it works for me, but it shows this results:
-------------------------------------------------<br />
category | Total_USD | Total_EUR | Total_SAR |<br />
-------------------------------------------------<br />
cat1 | 10,000 | 0 | 45,000 |<br />
cat2 | 30,000 | 8,000 | 0 |<br />
cat3 | 0 | 7,000 | 15,000 |<br />
cat4 | 0 | 0 | 0 |
what i want is: don't select results that has (0) values, like above in (cat4).