Creating custom fields

Reporting in SQL

Tyler Pernes

Learning & Development Consultant

Goal report

+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------+--------------------+-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
+----------+--------------------+-------+

SELECT athlete_id, gender, age, gold  
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
UNION ALL
SELECT athlete_id, gender, age, gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;

Preparation

Step 1: Comment out bottom half

SELECT athlete_id, gender, age, gold  
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

/*UNION ALL
SELECT athlete_id, gender, age, gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;*/

Preparation

Step 2: Add new field placeholders

SELECT 
    --___ AS season, 
    --___ AS demographic_group,
    --___ AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

/*UNION ALL
SELECT athlete_id, gender, age, gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;*/

Field 1: seasons

SELECT 
    'Summer' AS season,
    --___ AS demographic_group,
    --___ AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

Field 2: golds

SELECT 
    'Summer' AS season,
    --___ AS demographic_group,
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

+---------+-------+
| season  | golds |
|---------+-------|
| Summer  | 159   |
+---------+-------+

Field 3: demographic_group

+--------+-------+--------------------+
| gender | age   | demographic_group  |
|--------+-------+--------------------|
| M      | 18    | Male Age 13-25     |
| M      | 31    | Male Age 26+       |
| F      | 22    | Female Age 13-25   |
| F      | 26    | Female Age 26+     |
+--------+-------+--------------------+

CASE statement

CASE WHEN {condition_1} THEN {output_1}
WHEN {condition_2} THEN {output_2}
ELSE {output_3} 
END

Field 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN ___ THEN 'Male Age 13-25'
    WHEN ___ THEN 'Male Age 26+'
    WHEN ___ THEN 'Female Age 13-25'
    WHEN ___ THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

Field 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN ___ THEN 'Male Age 26+'
    WHEN ___ THEN 'Female Age 13-25'
    WHEN ___ THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

Field 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

ERROR: Column must be in a GROUP BY clause.

Field 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
GROUP BY demographic_group;

Query Ran Successfully!

Field 3: demographic_group

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
GROUP BY demographic_group;

No ELSE statement = easier to validate

New state of query

SELECT 
    'Summer' AS season,
    CASE WHEN age >= 13 AND age <= 25 AND gender = 'M' THEN 'Male Age 13-25'
    WHEN age > 25 AND gender = 'M' THEN 'Male Age 26+'
    WHEN age >= 13 AND age <= 25 AND gender = 'F' THEN 'Female Age 13-25'
    WHEN age > 25 AND gender = 'F' THEN 'Female Age 26+' 
    END AS demographic_group, 
    SUM(gold) AS golds
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
GROUP BY demographic_group
UNION ALL
SELECT 
    ...
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id
GROUP BY demographic_group;

Let's practice!

Reporting in SQL