I am struggling with a SQL query,
Query:
I want to find a list of hospitals with a count of dentists (is_denitist=true) and all doctors (including dentists) having monthly_income > 100 000
I have 2 tables Hospitals and Doctors with the following schema,
-------------
| Hospital |
|-----------|
| id | name |
|-----------|
---------------------------------------------------------
| Doctor |
|--------------------------------------------------------
| id | name | monthly_income | is_dentist | hospital_id |
|--------------------------------------------------------
The query I came up with is,
select h.name, count(d.is_dentist), sum(d.monthly_income)
from Hospital h inner join Doctor d
on h.id = d.hospital_id
where d.monthly_income > 100000 and d.is_dentist=true
group by h.name;
If I am a dentist and having income less than 100 000 then the hospital should still count me as a dentist.
But the caveat in the above query is it filters out all doctors having monthly_income above 100 000 and are dentists. I want an independent count of these conditions like predicates over each count() column. How can we achieve this in a single query?
You can do conditional aggregation.
Since is_dentist (presumably) contains 0/1 values, you can just sum() this column to count how many doctors belong to the group.
On the other hand, you can use another conditional sum() to count how many doctors have an income above the threshold.
select
h.name,
sum(d.is_dentist) no_dentists,
sum(d.monthly_income > 100000) no_doctors_above_100000_income
from Hospital h
inner join Doctor d on h.id = d.hospital_id
group by h.name;
You have two independent conditions (monthly_income > 100000, and is_dentist=true) which means there are two different data sets. You can't be used two different data set in the same group query. So you need to divide it into two subqueries. You can check the following query whether the result is you wanted:
select temp3.name, temp1.dentist_count, temp2.income_count from
(select d1.hospital_id, count(*) as dentist_count from Doctor d1 where d1.monthly_income>100000 group by d1.hospital_id) as temp1
join
(select d2.hospital_id, count(*) as income_count from Doctor d2 where d2.is_dentist=true group by d2.hospital_id) as temp2
on temp1.hospital_id=temp2.hospital_id
join
(select h.id, h.name from Hospital h) as temp3
on temp2.hospital_id=temp3.id;