Is there a way to join the following SELECT statements?-CodePudding

SELECT 
    date, 
    people_fully_vaccinated AS 'Australia'
FROM vaccinations 
WHERE location = 'Australia';
SELECT 
    date, 
    people_fully_vaccinated AS 'United States'
FROM vaccinations 
WHERE location = 'United States';
SELECT 
    date, 
    people_fully_vaccinated AS 'France'
FROM vaccinations 
WHERE location = 'France';
SELECT 
    date, 
    people_fully_vaccinated AS 'Israel'
FROM vaccinations 
WHERE location = 'Israel';

CodePudding user response：

Already u have location as country name. So no need to select column people_fully_vaccinated

if you want filter by particular country use below this:

SELECT date
    ,location
FROM vaccinations
WHERE location = 'Australia'
    OR location = 'United States'
    OR location = 'France'
    OR location = 'Israel'

if you want to get all, then use below this:

SELECT date,location FROM vaccinations;

CodePudding user response：

If you don't care about the figures for each country being on separate rows you can use:

select    date,
          location,
          people_fully_vaccinated
from      vaccinations
where     location in ('Australia','United States','France','Israel');

If you want the figures for each country to appear on the same row for each date, you can use:

select    date,
          sum(case when location = 'Australia' then people_fully_vaccinated else 0 end)     as "Australia",
          sum(case when location = 'United States' then people_fully_vaccinated else 0 end) as "United States",
          sum(case when location = 'France' then people_fully_vaccinated else 0 end)        as "France",
          sum(case when location = 'Israel' then people_fully_vaccinated else 0 end)        as "Israel"
where     location in ('Australia','United States','France','Israel')
group by  date;