Membuat field kustom

Pelaporan dengan SQL

Tyler Pernes

Learning & Development Consultant

Laporan target

+----------+--------------------+-------+
| 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;

Persiapan

Langkah 1: Komentari paruh bawah

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;*/

Persiapan

Langkah 2: Tambahkan placeholder field baru

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+     |
+--------+-------+--------------------+

Pernyataan CASE

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: Kolom harus ada dalam klausa GROUP BY.

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;

Kueri berhasil dijalankan!

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;

Tanpa pernyataan ELSE = lebih mudah divalidasi

Keadaan kueri baru

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;

Ayo berlatih!

Pelaporan dengan SQL