Last post Dec 15, 2017 05:47 PM by kmcnet
Dec 15, 2017 03:20 AM|kmcnet|LINK
Hello everyone and thanks for your help in advance. I am trying to develop a query that selects all records from a customer table while returning a count from an order table to return a customer id, customer name, and order count. I've played with various
types of Joins but haven't been able to figure out how to do this. Any help would be appreciated.
Dec 15, 2017 07:32 AM|cheah85|LINK
Select c.*,coalesce(OrderCount ,0) as OrderCount
from customer c left join (Select CustomerID, count(*) as OrderCount from [order] group by CustomerID) O on O.CustomerID = c.ID
Dec 15, 2017 10:56 AM|oned_gk|LINK
SELECT (SELECT count(*) from tableA) as A, (SELECT count(*) FROM TableB) as B
Dec 15, 2017 04:11 PM|kmcnet|LINK
Thanks for the response. This works perfectly, but truthfully, I have no idea how to write this on my own. I looked at the documentation for coalesce:
and I'm not following how this is applicable.
Thanks again for the help.
Dec 15, 2017 05:29 PM|mgebhard|LINK
Coalesce has no bearing on the logic and simply replaces NULLs with zeros due to the LEFT join on the subquery. A LEFT JOIN means give me ALL the records from the customer table joined by customer Id. If there is no matching ID in the order table then
show NULL but I want the customer record.
The real Magic is in the sub query, as stated above, which creates a resultset of customer Id and count on the order table. This result set is joined to the customer table by customer Id which pulls in the count.
Dec 15, 2017 05:47 PM|kmcnet|LINK
Ninja skills to me. Part of the problem for me was returning Nulls, which has now been explained. I appreciate everyone's help.