Learn From - Interactive SQL

Used SUBQUERY and Case statement to split a Column into two.
Below is shown the "ProfileInfo" table:

SQL using Case statement and SUBQUERY

In the "ProfileInfo" table the People Profiles Information is listed

In the SQL Query the DATEDIFF function is used to get the Age.

Here in the "ProfileInfo" table, the column PersonsSex contains only two values "Male" and "Female". Using the Case statement we can split the PersonsSex column into "MaleCount" and "FemaleCount" columns.

Text Editor below has the SQL Query, click the "View Result" button, to see the result.


Note: The PersonsSex column is now split as "MaleCount" and "FemaleCount" columns.

Above Query Executed Result:


In the query shown below, the above query is used as a Table with alias "tbl" in the From clause.

SQL Query to know how many males and females are born in an age group.


Note: The from Clause uses the SUBQUERY that acts as a table with the alias name "tbl". In the SUBQUERY Case Statements are used to seperate the MaleCount and FemaleCount. Later the GROUP BY is used to get the sum of MaleCount and FemaleCount.

SQL in Text Editor:

Select tbl.Age, SUM(tbl.MaleCount) as MaleCount, SUM(tbl.FemaleCount) as FemaleCount, (SUM(tbl.MaleCount) + SUM(tbl.FemaleCount)) as TotalCount From ( Select DATEDIFF(yyyy, DateOfBirth, GETDATE()) as Age , (Case When PersonsSex = 'Male' then 1 else 0 End) as MaleCount, (Case When PersonsSex = 'Female' then 1 else 0 End) as FemaleCount From ProfileInfo )as tbl Group By tbl.Age;