Use COUNT and JOIN in a same query SQL

Question

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.

Every column you select has to be in the group by clause. So use Group by b.Name — Shiffty
– Shiffty, Commented Oct 24, 2014 at 9:34

BeNdErR · Accepted Answer · 2014-10-24 09:39:15Z

1

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

answered Oct 24, 2014 at 9:39

BeNdErR

18k21 gold badges79 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shiffty · Accepted Answer · 2014-10-24 09:38:03Z

0

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;

answered Oct 24, 2014 at 9:38

Shiffty

2,1662 gold badges27 silver badges33 bronze badges

2 Comments

Sadikhasan Over a year ago

If same name in customer table then what happen?

Shiffty Over a year ago

Then group by Id first. GROUP BY a.Id, b.Name

Collectives™ on Stack Overflow

Use COUNT and JOIN in a same query SQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related