locked
Select * from ONe Table with Select Count from another RRS feed

  • Question

  • User1122355199 posted

    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.

    Friday, December 15, 2017 3:20 AM

Answers

  • User-189443255 posted

    Use subquery.

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 15, 2017 7:32 AM

All replies

  • User-189443255 posted

    Use subquery.

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 15, 2017 7:32 AM
  • User-1716253493 posted

    Try this

    SELECT (SELECT count(*) from tableA) as A, (SELECT count(*) FROM TableB) as B
    Friday, December 15, 2017 10:56 AM
  • User1122355199 posted

    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:

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

    and I'm not following how this is applicable.

    Thanks again for the help.

    Friday, December 15, 2017 4:11 PM
  • User475983607 posted

    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.

    Friday, December 15, 2017 5:29 PM
  • User1122355199 posted

    Ninja skills to me.  Part of the problem for me was returning Nulls, which has now been explained.  I appreciate everyone's help.

    Friday, December 15, 2017 5:47 PM