Average Consultation Fee by Specialty

Easy
205.

Average Consultation Fee by Specialty

EasyAggregation

MySQL

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

ColumnType
doctor_idint
namevarchar
specialtyvarchar
consultation_feeint

Primary key: doctor_id.

consultation_fee is the OPD fee in rupees, or NULL when not set yet.

Example

The rows must come back in the order shown.

Doctor

doctor_idnamespecialtyconsultation_fee
1Kavya MenonCardiology1200
2Rohan GuptaCardiology900
3Aisha KhanDermatology800
4Vikram JoshiDermatologyNULL
5Ira BoseENT700
6Dev MehtaENT800
7Tanvi RaoENT800
8Harsh DasNeurologyNULL

Output

specialtydoctorsavg_fee
Cardiology21050
Dermatology2800
ENT3766.67
Neurology1NULL
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.

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

ColumnType
doctor_idint
namevarchar
specialtyvarchar
consultation_feeint

Primary key: doctor_id.

consultation_fee is the OPD fee in rupees, or NULL when not set yet.

Examples

Example 1

Doctor

doctor_idnamespecialtyconsultation_fee
1Kavya MenonCardiology1200
2Rohan GuptaCardiology900
3Aisha KhanDermatology800
4Vikram JoshiDermatologyNULL
5Ira BoseENT700
6Dev MehtaENT800
7Tanvi RaoENT800
8Harsh DasNeurologyNULL

Output

specialtydoctorsavg_fee
Cardiology21050
Dermatology2800
ENT3766.67
Neurology1NULL

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, specialty

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