Insert `COUNT(*)` based on separate table

Question

In MySQL is it possible to select columns from one table while also creating a column for COUNT(*) based on other tables? That way a summary of the results from all tables can be returned. This might be a bit confusing to explain in words so I made some sample tables instead:

events_tbl
----------------------------
id      |   eventname
1       |   Anime Festival
2       |   Food Festival   
----------------------------

booths_tbl
-------------------------
id      |   boothname
1       |   Walmart
2       |   Pizza Hut
3       |   Nike
4       |   North Face
-------------------------

participants_tbl
-----------------------------
id      |   participantname
1       |   John
2       |   Mike
3       |   Rambo
4       |   Minnie
-----------------------------

event_booths_tbl
--------------------------------
event_id        |       booth_id
1               |       1
1               |       2
1               |       5
2               |       3
2               |       4
--------------------------------

event_participants_tbl
-------------------------------------
event_id        |       booth_id
1               |       1
1               |       2
1               |       3
1               |       4
-------------------------------------

Is there a way to get results like this in MySQL:

summary_tbl
------------------------------------------------------------------------
id  |   eventname       |   booth_count     |       participant_count
1   |   Anime Festival  |   3               |       4
2   |   Food Festival   |   2               |       0
------------------------------------------------------------------------

The `booths` and `participants` tables are irrelevant, this problem doesn't need the names. Are you missing `event_participants_table`? — Barmar, Jun 13 '14 at 10:32

score 1 · Answer 1 · answered Jun 13 '14 at 10:27

1

Join with subqueries that count in each table:

SELECT e.id, e.event_name, 
       IFNULL(b.booth_count, 0) AS booth_count, 
       IFNULL(p.participant_count, 0) AS participant_count
FROM events_table AS e
LEFT JOIN (SELECT event_id, COUNT(*) AS booth_count
           FROM event_booths_table
           GROUP BY event_id) AS b ON e.id = b.event_id
LEFT JOIN (SELECT event_id, COUNT(*) AS participant_count
           FROM event_participants_table
           GROUP BY event_id) AS p ON e.id = p.event_id

answered Jun 13 '14 at 10:27

Barmar

741,623
53
500
612

So many sub-queries! This would be a good read: [Join vs. sub-query](http://stackoverflow.com/questions/2577174/join-vs-sub-query) – enchance Jun 13 '14 at 13:54
That question is comparing `JOIN` with correlated sub-queries being used in the `SELECT` clause. That's not what I'm doing here. – Barmar Jun 13 '14 at 14:07
Point taken. This is different. – enchance Jun 13 '14 at 14:25

Sharmi · Accepted Answer · 2014-06-13T11:44:03.303

1

The event_participants_tbl should contain participant_id instead of booth_id. Its irrelevant otherwise. Your MySQL query would be like this :

select
 et.id,
 et.eventname,
 count(distinct ebt.booth_id) as booth_count,
 count(distinct ept.participant_id) as participant_count
from
 event_booths_tbl ebt
 left join events_tbl et on et.id=ebt.event_id
 left join event_participants_tbl ept on ept.event_id=ebt.event_id
group by et.event_id;

edited Jun 13 '14 at 11:44

answered Jun 13 '14 at 10:36

Sharmi

79
1
1
10

I suspect that's a typo he made when copying to SO. – Barmar Jun 13 '14 at 11:14
1

I'm marking this as correct except for a missing item: `count(ebt.id)` should be `count(DISTINCT ebt.booth_id)` and `count(ept.id)` should be `count(DISTINCT ept.participant_id)` – enchance Jun 13 '14 at 11:37
@enchance If you needed those `DISTINCT` options for the query to be right, why didn't you accept @CharveeShah's answer -- he had these options in his original answer before this one was corrected. – Barmar Jun 13 '14 at 14:09
@Barmar Easy, @Sharmi's answer looked closer to my coding style than the other. If it's a question of a missing `DISTINCT` against a typo and code readability, I'd go with the missing `DISTINCT` and clear things up in a single sentence. – enchance Jun 13 '14 at 14:30

score 0 · Answer 3 · answered Jun 13 '14 at 10:28

Try this :

    select event.id,
    event.name,
    count(distinct eventBooth.booth_id),
    count(distinct eventParitcipant.booth_id) 
    from events_tbl event
        LEFT JOIN event_booths_tbl eventBooth on eventBooth.event_id=event.id
        LEFT JOIN event_participants_tbl eventParitcipant 
                      on eventParitcipant.event_id=event.id
        group by event.id

Insert `COUNT(*)` based on separate table

3 Answers3