Get startedGet started for free

Creating custom fields

1. Creating custom fields

In this lesson, we will add another layer to our query by building custom fields.

2. Goal report

Here's a preview of the report we are trying to build, as well as by the current state of our query. So far, we've combined the tables through JOINs and UNIONs but kept placeholder fields in for now. Let's update the fields to correctly match the columns on the report.

3. Preparation

Since we have two sides of our query separated by a UNION, it is easier to work with one side at a time. As a preparation step, comment out the second half of the query so we can just focus on the top half. We can update the bottom after.

4. Preparation

Next, replace the fields with placeholders for our fields. Since we don't know how the fields should be setup yet, add blanks before each field and comment out each line with dashes. This setup allows us to efficiently test our code after each step.

5. Field 1: seasons

Let's start with the season field. We want to output 'Summer' for all summer games, which applies to the entire top half of the query. We can add a static field called season here, by just adding the string in single quotes. Simple as that!

6. Field 2: golds

Next, let's setup the golds field. For this, we just need a basic SUM function. Let's uncomment the field and test it out. Looks good!

7. Field 3: demographic_group

Now for the fun one: demographic_group. This requires us to bucket athletes based on their AGE and GENDER. To setup this type of conditional, we can use a CASE statement.

8. CASE statement

Here is a quick refresher on CASE statement syntax. In a case statement, we define a conditional and indicate what the output should be when that condition is satisfied. If no condition is satisfied, we output the ELSE statement. There's no limit on the number of allowed conditions.

9. Field 3: demographic_group

We want four different outputs for demographic_group, so I've updated the CASE statement to show all four outputs. Now, all we need to do is update the conditionals for each.

10. Field 3: demographic_group

An athlete that is male, age 13-25, must satisfy three separate conditions. Age should be at least 13, age should be no more than 25, and gender should be male. We can combine all three using AND statements, as shown.

11. Field 3: demographic_group

We can take similar approach for the other three conditionals. Here's the full case statement. Let's run this to make sure it works. Oh no! We get an error! It looks like we need to add a group by clause. This is because we introduced a dimension into a query that already contained an aggregation.

12. Field 3: demographic_group

So let's tell SQL to group by the new dimension. It works!

13. Field 3: demographic_group

Also, notice how we have no ELSE statement. This makes it so if no condition is satisfied, the report will output a "null". With this setup, if we see a "null", it's clear there is an issue that requires troubleshooting. If we had an "else" statement, we wouldn't be able to see any nulls, which means errors would not be easily identifiable.

14. New state of query

So that's all three fields! Remember, that was only for half the query, so let's replicate this code for the bottom half, making sure we update summer to winter. I added three dots near the bottom of our query to fit the slide, but note that it should show the same CASE statement as above.

15. Let's practice!

We're getting close! Before we move on to the final steps, let's practice creating some custom fields.