How to form this MYSQL query

Question

I have the following table

TABLE store

store   name   level
1       Tom    4
2       Joe    2
1       Chris  4
3       Tom    2
4       Ed     2
2       Tom    4
3       Chris  2

I want to return the number of level 4's from each distinct store I know I can

select distinct store from store;

To get distinct stores and

select count(*) as level from store where level = 4;

to get the count of level 4's

How do I combine to return a query of number of level 4's in each distinct store So the data above would return

store    level4
1        2
2        1
3        0
4        0

Answer 1

It is not clear why your table is called store. Shouldn't you have a table with that name that has one row per store?

In any case, probably the simplest method for getting the 0 counts is conditional aggregation:

select store, sum(level = 4) as level4
from store
group by store;