The no-show rate is the share of appointments that were due in which the patient did not come:
no-show rate = no-shows / (completed + no-shows)
A cancelled appointment was called off before the day, so it was never due; a scheduled one has not happened yet. Both stay out of the numerator and the denominator.
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'no-show')
/ COUNT(*), 1) AS no_show_pct
FROM hd_appointments
WHERE status IN ('completed', 'no-show');
Three mistakes give a wrong rate with no error message:
- dividing by all appointments, which makes the rate look lower;
- counting cancellations as no-shows, which makes it look higher;
- integer division:
no_shows / due of two whole numbers is 0. Multiply by 100.0 first.
No-shows over the appointments that were due.