Joins and Aggregate Functions 1

[dazzer123]

Hi I need to write a query that will show a list of retailers and a count of all of there orders.

I came up with this;

SELECT Retailers.NA_ID, Count(Orders.NA_ID) As countOfOrders
FROM Retailers
Left Outer Join
Orders
ON Retailers.NA_ID = Orders.NA_ID
WHERE (Orders.JointOR_ID <> '' OR Orders.OT_ID=2)
Group By
Retailers.NA_ID

Now the problem here is it shows all the retailers that have had at least one order but I want it to every retailer and a 0 Where they have had no orders.

Any help would be greatly appreciated.

 

[SimonSellick]

You can't include an unprocessed column from 'Orders' in the WHERE clause if the table is the optional end of an outer join, because when there is no row from Orders, the column (null) cannot pass that part of the WHERE clause.

You could use isnull(col, unlikely-val) and test for that, but the simplest way is to include all of the tests on Orders columns in the join condition:

SELECT r.NA_ID,
Count(o.NA_ID) countOfOrders
FROM Retailers r
LEFT JOIN Orders o
ON o.NA_ID = r.NA_ID
AND (Orders.JointOR_ID <> ''
OR Orders.OT_ID=2
)
GROUP BY r.NA_ID

HTH

 

[dazzer123]

Excellant,

Thanks a lot, I didn't realise this but now you've pointed it out its fairly obvious when you think about it.

Thanks again for your help