Is there a way for me to use multiple WHERE statements to select total population for 7 races group-CodePudding

I have this dataset that I imported to MySQL and I want to select only the total population for each race group by county. So far I can only select the total population of two races grouped by county like the below syntax (I created a View named asian to join the table named ca_pop):

select ca.county as County, sum(ca.population) as 'White pop', a.Asian_pop as Asian from asian as a
join ca_pop as ca on ca.county = a.county
where ca.date_year = '2022' and ca.race = 'White, Non-Hispanic'
group by County

Here's the syntax I wrote to create the table for the imported dataset:

CREATE TABLE ca_pop(
county_code INT NOT NULL,
county VARCHAR(45) NULL,
date_year VARCHAR(4) NULL,
race_code INT NULL,
race TEXT NULL,
gender VARCHAR(6) NULL,
age INT NULL,
population INT NULL);

And here's the syntax to create the View:

create view asian as select county, sum(population) as Asian_Pop from ca_pop
where date_year = 2022 and race = 'Asian, Non-Hispanic'
group by county

Is there a way for me to use multiple Where statements to select the total population for each of the 7 races by county?

CodePudding user response：

select ca.county as County, sum(SELECT population FROM ca_pop GROUP BY  race ) as 'White pop', a.Asian_pop as Asian from asian as a
join ca_pop as ca on ca.county = a.county
where ca.date_year = '2022' and ca.race = 'White, Non-Hispanic'
group by County

CodePudding user response：

I already found a solution by using CASE statement to pivot the table and the GROUP_CONCAT statement to pivot it as well. If anyone interested here's the link where you can find out more about PIVOT TABLE: https://www.databasestar.com/mysql-pivot/#ftoc-heading-4

CodePudding user response：

select county, race, sum(population)
from ca_pop
group by county, race