Average Consultation Fee by Specialty — SQL Problem
Before revising OPD prices the hospital wants the current fee picture.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #205
Problem statement
Before revising OPD prices the hospital wants the current fee picture. A doctor whose consultation_fee is NULL has no fee set yet: they count as a doctor of the specialty but are left out of the average.
Return specialty, doctors (all doctors of the specialty) and avg_fee (the average of the set fees, rounded to 2 decimals; NULL if no doctor of the specialty has a fee). Order by avg_fee descending, then specialty ascending — a NULL average sorts last.
Tables
Table: Doctor
| Column | Type |
|---|---|
| doctor_id | int |
| name | varchar |
| specialty | varchar |
| consultation_fee | int |
Primary key: doctor_id.
consultation_fee is the OPD fee in rupees, or NULL when not set yet.
Examples
Example 1
Doctor
| doctor_id | name | specialty | consultation_fee |
|---|---|---|---|
| 1 | Kavya Menon | Cardiology | 1200 |
| 2 | Rohan Gupta | Cardiology | 900 |
| 3 | Aisha Khan | Dermatology | 800 |
| 4 | Vikram Joshi | Dermatology | NULL |
| 5 | Ira Bose | ENT | 700 |
| 6 | Dev Mehta | ENT | 800 |
| 7 | Tanvi Rao | ENT | 800 |
| 8 | Harsh Das | Neurology | NULL |
Output
| specialty | doctors | avg_fee |
|---|---|---|
| Cardiology | 2 | 1050 |
| Dermatology | 2 | 800 |
| ENT | 3 | 766.67 |
| Neurology | 1 | NULL |
How to solve Average Consultation Fee by Specialty
Group by specialty and pick the aggregate that matches each column's rule. COUNT(*) counts every doctor row in the group, NULL fee or not. AVG(consultation_fee) skips NULLs, so a doctor without a fee does not drag the average towards zero — the average is over the doctors who have one. If none of them has a fee, AVG of no values is NULL, as the statement wants.
MySQL computes averages of integers as decimals with four places, so the result is rounded to 2 decimals to make it exact and comparable. The same value can be built by hand as SUM(fee) / COUNT(fee), with NULLIF to avoid dividing by zero when no fee is set.
For the order: MySQL treats NULL as smaller than any number, so ORDER BY avg_fee DESC puts a NULL average last; ties on the average fall back to the specialty name. One pass and one group per specialty.
Reference solution (MySQL)
SELECT specialty, COUNT(*) AS doctors, ROUND(AVG(consultation_fee), 2) AS avg_fee
FROM Doctor
GROUP BY specialty
ORDER BY avg_fee DESC, specialtyAnother way
SELECT specialty, COUNT(doctor_id) AS doctors, ROUND(SUM(consultation_fee) / NULLIF(COUNT(consultation_fee), 0), 2) AS avg_fee FROM Doctor GROUP BY specialty ORDER BY 3 DESC, 1← Doctor Names for the Prescription Letterhead · OPD No-Show Rate by Doctor →