Rama Lingam Rama Lingam - 3 months ago 17
MySQL Question

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

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!

Answer

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.