Search code examples
sqljoinalasql

SQL Query For These Two Tables

I have these two tables:

Person

Name    Gender  RegionID
Bob     Male    1
Mary    Female  2
Jane    Female  3
Chris   Male    1
Paul    Male    2
Matt    Male    2
Jenny   Female  1

Region

ID  Region  
1   North   
2   South   
3   Central

I would like to end up with a table like this:

Region  Male    Female
North   2       1
South   2       1
Central 0       1

I first tried to get the resulting table with the region's ID in there so no join required:

select RegionID, Gender, count(Name) from Person group by RegionID

But the Gender field is coming back undefined. And didn't really know where to go from there.

As tagged, I'm using the alasql JavaScript library.

Solution

  • Try to look at this:

    SELECT A.Region
    , SUM(CASE WHEN Gender = 'Male' Then 1 else 0 end) Male
    , SUM(CASE WHEN Gender = 'Female' Then 1 else 0 end) Female
FROM Region A
Inner Join Person B
On A.ID = B.RegionID
Group By A.Region