how to get count of records from 2 tables based on value in a particular column from 1st table using MySQL

166
April 21, 2018, at 10:54 PM

These are the tables I am fetching count from

register
+----+-------------+--------+
| id | empSignupId | cityId |
+----+-------------+--------+
| 42 |           4 |      1 |
| 47 |           3 |      1 |
| 48 |          11 |      1 |
| 54 |          20 |      1 |
| 55 |          21 |      2 |
| 56 |          22 |      2 |
+----+-------------+--------+
guest_list
+-----+------------+-------------+
| id  | guestName  | empSignupId |
+-----+------------+-------------+
| 103 | Mallica SS |           3 |
| 104 | Kavya      |           3 |
| 108 | Vinay BR   |          11 |
| 109 |  Akash MS  |          11 |
+-----+------------+-------------+
cities
+----+---------------+
| id | cityName      |
+----+---------------+
|  1 | Bengaluru     |
|  2 | Chennai       |
|  3 | Sydney        |
|  4 | New York City |
|  5 | Shanghai      |
|  6 | Chicago       |
+----+---------------+

I need to fetch the count of people registered from particular city which includes people, their guests, if guests are not present also it should show the count of people.

This is what I tried

mysql> SELECT COUNT(gl.id) +  COUNT(rfs.id), ct.cityName, rfs.cityId  FROM regis
ter rfs INNER JOIN cities ct ON ct.id=rfs.cityId INNER JOIN guest_list g
l ON gl.empSignupId = rfs.empSignupId GROUP BY rfs.cityId;
+-------------------------------+-----------+--------+
| COUNT(gl.id) +  COUNT(rfs.id) | cityName  | cityId |
+-------------------------------+-----------+--------+
|                             8 | Bengaluru |      1 |
+-------------------------------+-----------+--------+

I also need the count of people from other cities to be displayed, since there are no guests from some cities its not returning that count.

Please help me figure this out, I am still new to MySQL.. any help is greatly appreciated.

Answer 1

You have to use a LEFT JOIN to the guest's list table in order to also get cities without guests:

SELECT COALESCE(countGuest, 0) + COUNT(rfs.id), ct.cityName, rfs.cityId  
FROM register rfs 
INNER JOIN cities ct ON ct.id=rfs.cityId 
LEFT JOIN (
  SELECT empSignupId, COUNT(empSignupId) AS countGuest
  FROM guest_list gl 
  GROUP BY empSignupId
) gl  ON gl.empSignupId = rfs.empSignupId 
GROUP BY rfs.cityId;

Output:

COALESCE(countGuest, 0) + COUNT(rfs.id) cityName    cityId
4                                       Bengaluru   1
2                                       Chennai     2

Demo here

Note: If you also want to get cities without registrations then you need to place the cities table first and use a LEFT JOIN to register.

Note 2: You need to JOIN to an already grouped version of guest_list table in order to properly count guests.

Answer 2

Use LEFT JOINS and add count(distinct r.empSignupId) + count(distinct g.id):

select 
    c.id as cityId,
    c.cityName,
    count(distinct r.empSignupId) + count(distinct g.id) as people_count
from cities c
left join register r on r.cityId = c.id
left join guest_list g on g.empSignupId = r.empSignupId
group by c.id;

The result would be:

| cityId |      cityName | people_count |
|--------|---------------|--------------|
|      1 |     Bengaluru |            8 |
|      2 |       Chennai |            2 |
|      3 |        Sydney |            0 |
|      4 | New York City |            0 |
|      5 |      Shanghai |            0 |
|      6 |       Chicago |            0 |

Demo: http://rextester.com/OTBH14189

If you don't need the rows with 0, change the first LEFT JOIN to an inner JOIN.

Rent Charter Buses Company
READ ALSO
MySQL: Data goes blank when try to Update a row

MySQL: Data goes blank when try to Update a row

So I'm trying to update the Address of a Member in a tableAfter I click submit on the running page on the browser, the Address value inside the database went blank

178
SQL, matching all tags

SQL, matching all tags

I suppose that there is a really simple solution which I just can not see

167
Can we create Binary Tree in java using JDBC with all operations?

Can we create Binary Tree in java using JDBC with all operations?

Can we create Binary Tree in Java using JDBC with all operations like insert Node, search node, delete the node, sort tree etc?

129
sqlAlchemy execute a select row for the second time, it return nothing

sqlAlchemy execute a select row for the second time, it return nothing

I select and update a row data by sqlalchemy, when I execute the second time, sqlalchemy return None to me, why?

222