AN_SH AN_SH - 19 days ago 4
SQL Question

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 giving incorrect results. please advise.

Answer

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.
Comments