MYSQL /SQL multiple alias base on column values

Question

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

Consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Apr 21, 2020 at 19:24

Gordon Linoff · Accepted Answer · 2020-04-21 18:44:30Z

1

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;

answered Apr 21, 2020 at 18:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lekia Over a year ago

Isn't there suppose to be a join somewhere?

Gordon Linoff Over a year ago

@Lekia . . . A JOIN doesn't seem necessary.

Lekia Over a year ago

Thanks, @Gordon Linoff I just made a little tweak to it and got it working.

Collectives™ on Stack Overflow

MYSQL /SQL multiple alias base on column values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related