CODEWITHSUNDEEP
mysql
ArturoO
ArturoO

Reputation: 611

Get the count of records for table with foreign key

I have the following tables:

Persons:

person_id   |   name    | 
-------------------------
1           |   John    |
2           |   Adam    |
3           |   Elen    |
-------------------------

Orders:

order_id    |   person_id   |   product     | 
---------------------------------------------
1           |       1       |   TV          |
2           |       1       |   Radio       |
3           |       1       |   toothbrush  |
4           |       2       |   mp3 player  |
5           |       2       |   watch       |
6           |       3       |   ps 3        |
---------------------------------------------

Now I need to query above tables to get the following result:

person_id   |   name    |   order_count |
-----------------------------------------
1           |   John    |       3       |
2           |   Adam    |       2       |
3           |   Elen    |       1       |
-----------------------------------------

I tried something like:

SELECT u.person_id, u.name, COUNT(o.order_id) FROM persons AS p LEFT JOIN orders AS o ON (p.person_id=o.person_id);

unfortunately this doesn't work. Do you have any idea how to solve this ?

Upvotes: 0

Views: 69

Answers (5)

Mohammad Zare
Mohammad Zare

Reputation: 11

select p.person_id, p.name, o.cnt
from Persons p
left join 
(select person_id  , count(order_id) as cnt from Orders group by person_id) o
on p.person_id = o.person_id

Upvotes: -1

Sivakishore Teru
Sivakishore Teru

Reputation: 216

Try this

select o.person_id,p.name,count(o.person_id) as order_count from tblorders o 
Join tblpersons p on o.person_id=p.person_id
group by o.person_id,p.name

Upvotes: 0

Sivakishore Teru
Sivakishore Teru

Reputation: 216

Try this....

"select o.person_id,p.name,count(o.person_id) as order_count from tblorders o Join tblpersons p on o.person_id=p.person_id group by o.person_id,p.name"

Upvotes: 0

Asylzat
Asylzat

Reputation: 229

TRY THIS

SELECT persons.id, persons.name, COUNT(orders.id) AS order_count  FROM orders LEFT JOIN persons ON (persons.person_id=orders.person_id) GROUP BY persons.name

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42753

select Persons.person_id, Persons.name, p.order_count 
from Persons
inner join (select person_id, count(*) as order_count from Orders group by person_id) p
on Persons.person_id = p.person_id

If you need also get that persons, who doesn't have any orders, then use left join instead of inner join

Upvotes: 2

Related Questions