CODEWITHSUNDEEP
mysqlsql
Lekia
Lekia

Reputation: 184

MYSQL /SQL multiple alias base on column values

I have three tables: states (id, name); hospitals (id, name, level_id, state_id); level (id, name). The table (level) shows the quality of each hospital. A States can have several hospitals that have different quality of service (level). I want to achieve a MySQL query like below

Tables :

level 

id| name    |  
1 | level 1 |  
2 | level 2 | 
3 | level 3 |

hospitals 

id| name | level_id | state_id  
1 | Hos A| 1        | 22  
2 | Hos B| 3        | 7   
3 | Hos C| 2        | 13  
...

result: 

states | level 1 | level 2| level 3 | total
state 1| 0       | 1      | 1       | 2
state 2| 3       | 4      | 7       | 14  
...

I have tried something along this line but just got stuck. Any help will be appreciated

SELECT state.name states, hos.level_id 'level 1', hos.level_id 'level 2', hos.level_id 'level 3'
FROM state

LEFT JOIN hospital hos ON hos.state_id = state.id

ORDER BY state.name

Upvotes: 0

Views: 162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Is this what you want?

select state,
       sum(level_id = 1) as num_1,
       sum(level_id = 2) as num_2,
       sum(level_id = 3) as num_3,
       count(*) as total
from hospitals h
group by state;

Upvotes: 1

Related Questions