MySQL-Count rows in more than one table with WHERE condition

Question

When I'm run below the query separately getting the correct result.

SELECT COUNT(hospital_id) FROM `hospital` WHERE org_id='1' // Result: 0
SELECT COUNT(pharmacy_id) FROM `pharmacy` WHERE org_id='1' // Result: 1
SELECT COUNT(fire_station_id) FROM `fire_station` WHERE org_id='1' // Result: 3
SELECT COUNT(als_id) FROM `als` WHERE org_id='1' // Result: 3
SELECT COUNT(units_id) FROM `units` WHERE org_id='1'; //Result: 3

But I need all the result in single using any concept of MySQL.

My try:

SELECT COUNT(hospital_id) AS Hospital FROM `hospital` WHERE org_id='1'
UNION
SELECT COUNT(pharmacy_id) AS Pharmacy FROM `pharmacy` WHERE org_id='1'
UNION
SELECT COUNT(fire_station_id) AS Station FROM `fire_station` WHERE org_id='1'
UNION
SELECT COUNT(als_id) AS Als FROM `als` WHERE org_id='1'
UNION
SELECT COUNT(units_id) AS Units FROM `units` WHERE org_id='1';

This is my current result:

    +------------+
    |   Hospital |
    +------------+
    |          0 |
    |          1 |
    |          3 |
    +------------+

This is my desired result:

+------------+------------+------------+-------+--------+
|   Hospital |   Pharmacy |   Station  |  Als  |   Units|
+------------+------------+------------+-------+--------+
|          0 |          1 |         3  |     3 |      3 |
+------------+------------+------------+-------+--------+

I'm also refer the Stack Question Count rows in more than one table with tSQL But not getting the desired result. Please update my query or suggest any other concept of MySQL.

Thanks To ALL!

score 2 · Accepted Answer · answered Sep 03 '16 at 12:14

The problem with your specific query is UNION. Use UNION ALL. In fact, always use UNION ALL, unless you specifically want to incur the overhead of removing duplicates.

In any case, your queries are close. Put them as subqueries in the SELECT:

SELECT (SELECT COUNT(hospital_id) AS Hospital FROM `hospital` WHERE org_id='1') as hospital,
       (SELECT COUNT(pharmacy_id) AS Pharmacy FROM `pharmacy` WHERE org_id='1') as pharmacy,
       (SELECT COUNT(fire_station_id) AS Station FROM `fire_station` WHERE org_id='1') as Station,
       (SELECT COUNT(als_id) AS Als FROM `als` WHERE org_id='1') as als,
       (SELECT COUNT(units_id) AS Units FROM `units` WHERE org_id='1') as units;

You can also put the subqueries in the FROM clause and use CROSS JOIN to combine them.

This is working fine and good explanation. Thanks. It's really good. — Ramalingam Perumal, Sep 03 '16 at 12:30

score 1 · Answer 2 · answered Sep 03 '16 at 12:17

Try this:

SELECT
    (SELECT COUNT(hospital_id) FROM hospital WHERE org_id='1') as Hospital,
    (SELECT COUNT(pharmacy_id) FROM pharmacy WHERE org_id='1') as Pharmacy,
    (SELECT COUNT(fire_station_id) FROM fire_station WHERE org_id='1') as Station,
    (SELECT COUNT(als_id) FROM als WHERE org_id='1') as Als,
    (SELECT COUNT(units_id) FROM units WHERE org_id='1') as Units;

MySQL-Count rows in more than one table with WHERE condition

2 Answers2