Completed OPD Visits per Department in March 2025

Easy
200.

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.

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

ColumnType
doctor_idint
namevarchar
departmentvarchar

Primary key: doctor_id.

Table: Appointment

ColumnTypeValues
appt_idint
doctor_idint
patient_idint
appt_datedate
statusenumscheduled, completed, no_show, cancelled

Primary key: appt_id.

One row per OPD booking. doctor_id always exists in Doctor.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Doctor

doctor_idnamedepartment
1Kavya MenonCardiology
2Rohan GuptaCardiology
3Aisha KhanDermatology
4Vikram JoshiENT

Appointment

appt_iddoctor_idpatient_idappt_datestatus
10115012025-03-01completed
10225022025-03-14completed
10335032025-03-31completed
10435042025-04-01completed
10545052025-03-20no_show
10615062025-02-28completed
10725072025-03-22cancelled

Output

departmentcompleted_visits
Cardiology2
Dermatology1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
doctor_idint
namevarchar
departmentvarchar

Primary key: doctor_id.

Table: Appointment

ColumnType
appt_idint
doctor_idint
patient_idint
appt_datedate
statusenum(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_idnamedepartment
1Kavya MenonCardiology
2Rohan GuptaCardiology
3Aisha KhanDermatology
4Vikram JoshiENT

Appointment

appt_iddoctor_idpatient_idappt_datestatus
10115012025-03-01completed
10225022025-03-14completed
10335032025-03-31completed
10435042025-04-01completed
10545052025-03-20no_show
10615062025-02-28completed
10725072025-03-22cancelled

Output

departmentcompleted_visits
Cardiology2
Dermatology1

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.department

Another 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.department

Another 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 →