Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Goalie35
Yak Posting Veteran
81 Posts |
Posted - 2008-04-29 : 09:48:58
|
I have a query that needs to find all orders placed for a given month, sorted by category, however my query isn't returning categories that had zero products sold for the month. So my question is, just as an example, if I had three tables to find total order counts, but one of the products didn't sell any that month, how would I display it on the report?:Table: CategoriesCategoryID | CategoryName--------------------------- 1 | Cat1 2 | Cat2Table: ProductsProductId | ProductName | ProductCategoryId ----------------------------------------------------- 1 | Prod1 | 1 2 | Prod2 | 2Table: OrdersOrderID | OrderDate | ProductID----------------------------------------------------- 1 | 4/18/2008 | 1 2 | 4/19/2008 | 1 3 | 4/23/2008 | 1========================================================= In this case, my results should be the "Cat1" category has 3 and the "Cat2" category has 0, however my query only displays "Cat1". Since "Cat2" has zero, it ignores it.How would I setup my query to include "cat2" with its zero value as well?Thanks. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-29 : 10:42:34
|
[code]SELECT c.CategoryID, SUM(CASE WHEN o.OrderID IS NULL THEN 0 ELSE 1 END) AS [Count]FROM Categories cINNER JOIN Products pON p.ProductCategoryId=c.CategoryIDLEFT JOIN Orders oON o.ProductID=p.ProductIdGROUP BY c.CategoryID[/code] |
|
|
|
|
|