views:

313

answers:

3

I have a TSQL Query that does something like this:

SELECT SUM(s.Amount) as TotalSales, p.ProductName
FROM SALES s 
INNER JOIN Product p ON s.ProductID = p.ID
GROUP BY p.ProductName

The resulting output is

TotalSales  Product
-----------------------
123.45      Apples
234.56      Oranges
345.67      Grapes

What I would like to do is get ALL the products in the results, even the ones that have no sales. I tried doing a LEFT JOIN on the product table, but that just confuses things.

So I would like my output to be something like this.

TotalSales  Product
-----------------------
123.45      Apples
234.56      Oranges
345.67      Grapes
0.0         Lemons
0.0         Grapefruit

Any idea how to do this?

+3  A: 
SELECT SUM(ISNULL(s.Amount,0)) as TotalSales, p.ProductName
FROM SALES s 
RIGHT JOIN Product p ON s.ProductID = p.ID
GROUP BY p.ProductName
THEn
Using a RIGHT JOIN will turn up NULL `PRODUCT.PRODUCTNAME` records in the output. The correct approach is to LEFT JOIN onto the optional table (`SALES` in this case).
OMG Ponies
By the way, try not to mix `LEFT JOIN` with `RIGHT JOIN` later on when this query evolved. It WILL DRIVE YOU NUTS!!! (see i am using all caps just thinking about it)
Sung Meister
+1  A: 
SELECT COALESCE(SUM(s.Amount), 0) as TotalSales, p.ProductName
FROM Product p
LEFT JOIN SALES s ON s.ProductID = p.ID
GROUP BY p.ProductName
najmeddine
I think you need to wrap the SUM(s.Amount) in a coalesce to get the "0"
Gratzy
right, thanks .
najmeddine
+2  A: 

With left join (more readeability):

SELECT SUM(ISNULL(s.Amount,0)) as TotalSales, p.ProductName
FROM Product p 
LEFT JOIN SALES s ON p.ProductID = s.ID
GROUP BY p.ProductName
eKek0