Planning the query

Reporting in SQL

Tyler Pernes

Learning & Development Consultant

Chapter goal

Reporting in SQL

Questions to ask

  • What tables do we need to pull from?
  • How should we combine the tables?
  • What fields do we need to create?
  • What filters need to be included?
  • Any ordering or limiting needed?
Reporting in SQL

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     |
+----------+--------------------+-------+
Reporting in SQL

1 - What tables do we need to pull from?

Reporting in SQL

2 - How should we combine the tables?

Reporting in SQL

2 - How should we combine the tables?

Reporting in SQL

2 - How should we combine the tables?

Reporting in SQL

2 - How should we combine the tables?

Reporting in SQL

3 - What fields do we need to create?

+----------+--------------------+-------+
| 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()
Reporting in SQL

4 - What filters need to be included?

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 or HAVING?
  • Filter on dimension = WHERE
Reporting in SQL

5 - Any ordering or limiting needed?

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     |
+----------+--------------------+-------+
  • No LIMIT needed
  • Sort by golds in descending order
Reporting in SQL

Let's practice!

Reporting in SQL

Preparing Video For Download...