Planera frågan

Rapportering i SQL

Tyler Pernes

Learning & Development Consultant

Kapitelns mål

Frågor att ställa

Vilka tabeller behöver vi hämta från?
Hur ska vi kombinera tabellerna?
Vilka fält behöver vi skapa?
Vilka filter ska inkluderas?
Behövs någon sortering eller begränsning?

Scenario

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
| Winter   | Female Age 13-25   | 4     |
| Summer   | Female Age 26+     | 2     |
+----------+--------------------+-------+

1 - Vilka tabeller behöver vi hämta från?

2 - Hur ska vi kombinera tabellerna?

3 - Vilka fält behöver vi skapa?

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

season - static string
demographic_group - conditional
golds - SUM()

4 - Vilka filter ska inkluderas?

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+

WHERE eller HAVING?
Filtrera på dimension = WHERE

5 - Behövs sortering eller begränsning?

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+

Ingen LIMIT behövs
Sortera på golds i fallande ordning

Nu kör vi en övning!

Rapportering i SQL