Completed OPD Visits per Department in March 2025
The outpatient department (OPD) books appointments with doctors, and each doctor belongs to one department. Management wants March 2025's workload.
- Difficulty: Easy
- Topics: Aggregation, Dates, Joins
- Dialect: MySQL
- Problem: #200
Problem statement
The outpatient department (OPD) books appointments with doctors, and each doctor belongs to one department. Management wants March 2025's workload.
For each department, return department and completed_visits: the number of appointments with status completed whose appt_date falls in March 2025. Departments with no completed visit that month are left out. Return the rows in any order.
Tables
Table: Doctor
| Column | Type |
|---|---|
| doctor_id | int |
| name | varchar |
| department | varchar |
Primary key: doctor_id.
Table: Appointment
| Column | Type |
|---|---|
| appt_id | int |
| doctor_id | int |
| patient_id | int |
| appt_date | date |
| status | enum(scheduled, completed, no_show, cancelled) |
Primary key: appt_id.
One row per OPD booking. doctor_id always exists in Doctor.
Examples
Example 1
Doctor
| doctor_id | name | department |
|---|---|---|
| 1 | Kavya Menon | Cardiology |
| 2 | Rohan Gupta | Cardiology |
| 3 | Aisha Khan | Dermatology |
| 4 | Vikram Joshi | ENT |
Appointment
| appt_id | doctor_id | patient_id | appt_date | status |
|---|---|---|---|---|
| 101 | 1 | 501 | 2025-03-01 | completed |
| 102 | 2 | 502 | 2025-03-14 | completed |
| 103 | 3 | 503 | 2025-03-31 | completed |
| 104 | 3 | 504 | 2025-04-01 | completed |
| 105 | 4 | 505 | 2025-03-20 | no_show |
| 106 | 1 | 506 | 2025-02-28 | completed |
| 107 | 2 | 507 | 2025-03-22 | cancelled |
Output
| department | completed_visits |
|---|---|
| Cardiology | 2 |
| Dermatology | 1 |
How to solve Completed OPD Visits per Department in March 2025
An appointment knows its doctor but not the department, so the first step is a join from Appointment to Doctor. Then filter the rows down to what is being counted — status completed and a date inside March 2025 — and group what remains by department; COUNT(*) per group is the answer.
Because the filter runs before grouping, a department whose doctors had only no-shows or visits in other months has no rows left and does not appear, which is what the statement asks. The month test can be written as BETWEEN '2025-03-01' AND '2025-03-31' (dates compare correctly as fixed-format text, and both ends are inclusive), as YEAR(...) = 2025 AND MONTH(...) = 3, or with DATE_FORMAT(..., '%Y-%m'). The range form is the one an index on appt_date can use.
Conditional aggregation also works — count with a CASE inside SUM — but then the departments with a zero total must be removed with HAVING.
Reference solution (MySQL)
SELECT d.department, COUNT(*) AS completed_visits
FROM Appointment a
JOIN Doctor d ON d.doctor_id = a.doctor_id
WHERE a.status = 'completed'
AND a.appt_date BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY d.departmentAnother way
SELECT d.department, COUNT(a.appt_id) AS completed_visits FROM Doctor d JOIN Appointment a ON a.doctor_id = d.doctor_id AND a.status = 'completed' AND YEAR(a.appt_date) = 2025 AND MONTH(a.appt_date) = 3 GROUP BY d.departmentAnother way
SELECT department, SUM(CASE WHEN DATE_FORMAT(a.appt_date, '%Y-%m') = '2025-03' AND a.status = 'completed' THEN 1 ELSE 0 END) AS completed_visits FROM Appointment a JOIN Doctor d ON d.doctor_id = a.doctor_id GROUP BY department HAVING completed_visits > 0← Controlled Drug Prescriptions With Their Prescriber · Medicine Batches Expiring Within 90 Days →