I am writing a query that should return a report of the total number of public health workers working in the facility, the total number of doses people have received in the facility, and the total number of doses scheduled by people to be vaccinated in the facility in the future. I divided the task by three SQL codes:

-- 1 total number of public health workers working in the facility

SELECT `name`,  address, phone, type, phone, capacity, COUNT(person_id) AS totalWorkers
FROM healthcare_worker
INNER JOIN facility ON facility.`name`= healthcare_worker.facility_name
GROUP BY healthcare_worker.facility_name

-- 2 the total number of doses people have received in the facility

SELECT `name`,  address, phone, type, phone, capacity, SUM(dose) AS totalDose
FROM vaccination
INNER JOIN facility ON facility.`name`= vaccination.location
GROUP BY vaccination.location

-- 3 the total number of doses scheduled by people to be vaccinated in the facility in the future

SELECT `name`,  address, phone, type, phone, capacity, COUNT(booking_id) AS totalBookings 
FROM booking
INNER JOIN facility ON facility.`name`= booking.facility_name
GROUP BY booking.facility_name

Can I put them together into one query?

CodePudding user response：

You could use correlated sub-queries

SELECT `name`, address, phone, `type`, capacity
, ( SELECT COUNT(person_id)
    FROM healthcare_worker
    WHERE fac.`name`= healthcare_worker.facility_name
  ) AS totalWorkers
, ( SELECT SUM(dose)
    FROM vaccination
    WHERE fac.`name`= vaccination.location
  ) AS totalDose
, ( SELECT COUNT(booking_id)
    FROM booking
    WHERE fac.`name`= booking.facility_name
  ) AS totalBookings
FROM facility AS fac

db<>fiddle here