कस्टम फ़ील्ड बनाना

SQL में रिपोर्टिंग

Tyler Pernes

Learning & Development Consultant

लक्ष्य रिपोर्ट

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

तैयारी

चरण 1: निचला आधा भाग कमेंट करें

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

तैयारी

चरण 2: नए फ़ील्ड प्लेसहोल्डर जोड़ें

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

फ़ील्ड 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;

फ़ील्ड 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   |
+---------+-------+

फ़ील्ड 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 स्टेटमेंट

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

फ़ील्ड 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;

फ़ील्ड 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;

फ़ील्ड 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.

फ़ील्ड 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!

फ़ील्ड 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;

बिना ELSE स्टेटमेंट = वैलिडेट करना आसान

क्वेरी की नई स्थिति

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;

अभ्यास करते हैं!

SQL में रिपोर्टिंग