MySQL COUNT from multiple tables in single query

[thara]

In my DB schema, there are 4 tables and its relationship as shown in the below attached image.

My question is how many schools are there according to the data in the school table and how many of those schools belong to provinces, districts and zones, is it possible in one mysql query?

Currently I am using 4 separate queries for this and the relevant code is as follows.
 

$sql = "SELECT count(school_id) as schtot FROM sn_school";
$stmt = $pdo->query($sql);
$schtot = $stmt->fetchColumn();
$schtot = str_pad($schtot , 3, 0, STR_PAD_LEFT);

$sql = "SELECT count(zone_id) as zonetot FROM sn_school GROUP BY zone_id";
$stmt = $pdo->query($sql);
$zonetot = $stmt->rowCount();
$zonetot = str_pad($zonetot , 3, 0, STR_PAD_LEFT);

$sql = "SELECT COUNT(d.district_id) as districtTot
          FROM sn_school s
            JOIN zone z USING(zone_id)
            JOIN district d ON d.district_id = z.district_id
          GROUP BY d.district_id";
$stmt = $pdo->query($sql);
$districtTot = $stmt->rowCount();
$districtTot = str_pad($districtTot , 3, 0, STR_PAD_LEFT);

$sql = "SELECT COUNT(p.province_id) as ptot
          FROM sn_school s
            JOIN zone z USING(zone_id)
            JOIN district d ON d.district_id = z.district_id
            JOIN district p ON p.province_id = d.province_id
          GROUP BY p.province_id";
$stmt = $pdo->query($sql);
$ptot = $stmt->rowCount();
$ptot = str_pad($ptot , 3, 0, STR_PAD_LEFT);

 

 

[gizmola]

The number of schools is the number of rows in the school table. 

Your model implements a hierarchy:

province
  ├─ district
     ├─ zone
        ├─ school

 

To get counts of schools by Province/District and Zone, you can use GROUP BY province, district, zone WITH ROLLUP.  You should read about how WITH ROLLUP works, so you understand the "extra" rows it will produce.

SELECT p.province_id, d.district_id, z.zone_id, count(s.school_id) as countof
FROM sn_school s        
JOIN zone z USING(zone_id)
JOIN district d ON d.district_id = z.district_id
JOIN district p ON p.province_id = d.province_id
GROUP BY p.province_id, d.district_id, z.zone_id WITH ROLLUP