How do I query a list of countries in separate boxes?

Question

I want to produce a report that lists all the observation dates and, for each date, lists the total number of people fully vaccinated in each one of the 4 countries used in this assignment. people_fully_vaccinated needs to be inner join with location the first image is the format required.

the second image contains the data:

Answer 1

you can use:

select date, 
       sum(case when country = 'country 1' then vacinated_count else 0 end) as country1count, 
       sum(case when country = 'country 2' then vacinated_count else 0 end) as country2count,
       ...
       ...
from table 
group by date

considering you have vaciniated people count ready for each country in a table.