CODEWITHSUNDEEP
sql
phuwin
phuwin

Reputation: 3260

Use COUNT and JOIN in a same query SQL

My tables:

Customers:

+------+----+
| Name | ID |
+------+----+
| Phu  | 12 |
| Nam  | 23 |
| Mit  | 33 |
+------+----+

Orders:

+----+------------+
| ID |   Order    |
+----+------------+
| 12 | Laptop     |
| 12 | Mouse      |
| 33 | Smartphone |
| 23 | Keyboard   |
| 33 | Computer   |
+----+------------+

I want to get output like this:

+------+--------+
| Name | Orders |
+------+--------+
| Phu  |      2 |
| Mit  |      2 |
+------+--------+

I use this query but this doesn't work:

SELECT 
    Name,
    COUNT(*) AS 'Orders'
FROM 
    Orders a
INNER JOIN 
    Customers b ON a.ID = b.ID
GROUP BY 
    a.ID 
HAVING 
    COUNT(*) > 1;

It has the error like this:

Column 'Customers.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any help is really appreciated. Thank you.

Upvotes: 0

Views: 154

Answers (2)

BeNdErR
BeNdErR

Reputation: 17927

SELECT c.Name, COUNT(o.ID) AS Orders
FROM Customers c INNER JOIN Orders o ON c.ID = o.ID
GROUP BY o.ID
HAVING Orders > 1

Working Sqlfiddle: http://sqlfiddle.com/#!2/869790/4

Upvotes: 1

Shiffty
Shiffty

Reputation: 2156

As mentioned in my comment every column in the select statement has to be in the group by clause.

SELECT  Name, COUNT(*) AS 'Orders'
FROM    Orders a
INNER JOIN  Customers b
ON a.ID = b.ID
GROUP BY b.Name
HAVING COUNT(*)>1;

Upvotes: 0

Related Questions