CODEWITHSUNDEEP
sql
AN_SH
AN_SH

Reputation: 217

Select Customer Name From Customer Table and Count all Orders placed using orders table- SQL

I have two table as defined below,

Table: Customer:

 CustomerID | CustomerName | 
 --------------------------
    1       | John
    2       | Mack
    3       | Andy

Table: Orders:

   OrderID | CustomerID| 
 --------------------------
    1515   | 1
    1516   | 3
    1517   | 1
    1518   | 1
    1519   | 3
    1520   | 1

I want to write a query to select each name and count of all orders placed by each customer using JOIN. the result will be,

John   | 4
Mack   | 0
Andy   | 2

My query:

SELECT 
    CustomerName, Count(*) 
FROM 
    Orders
INNER JOIN 
    Customers WHERE Orders.CustomerID = Customers.CustomerID;

But its returning incorrect results. Please advise.

Upvotes: 1

Views: 12996

Answers (3)

Mansoor
Mansoor

Reputation: 4192

SELECT CustomerName,ISNULL(_Count,0) [Count]

FROM Customer LEFT OUTER JOIN ( SELECT COUNT(*) _Count,CustomerId _CustomerId FROM Orders GROUP BY CustomerId ) A ON _CustomerId = CustomerID

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

Use:

SELECT Customers.CustomerName, Count(*)
FROM Orders LEFT OUTER JOIN Customers
WHERE Orders.CustomerID=Customers.CustomerID GROUP BY Customers.CustomerName;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You are missing the GROUP BY. I would write the query like this:

SELECT c.CustomerName, Count(o.CustomerId)
FROM Customers c LEFT JOIN
     Orders o 
     ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerName;

Notes:

  • Table aliases (c and o) make the query easier to write and to read.
  • The LEFT JOIN keeps all customers, even those without orders. If you don't want 0 counts, then change to an INNER JOIN.
  • All joins should have an ON clause, not a WHERE clause for the JOIN conditions.
  • The GROUP BY is also needed to fix your query attempt.

Upvotes: 1

Related Questions