Learn to combine SQL CASE statements with COUNT, SUM, and AVG functions using Liverpool match data. The tutorial shows how to count wins by location, sum goals, and calculate win percentages by encoding wins as 1 and losses as 0 inside aggregate functions.
Chapters
Great job so far. Let's take a look at case statements with aggregate functions. Case statements can be used to create columns for categorizing data and to filter data. In your where clause, you can also use case statements to aggregate data based on the results of a logical test. Lets say you wanted to prepare a summary table counting the number of home and away games that Liverpool won in each season. If you've created summary tables and spreadsheets, you can probably visualize the final table here. But how do you get a count of Liverpool's wins in each season? You guessed it, a case statement. Case statements are like any other column in your query, so you can include them inside an aggregate function. Take a look at the case statement. The when clause includes a similar logical test to the previous lesson. Did Liverpool play as the home team and did the home team score higher than the away team? The difference begins in your then clause. Instead of returning a string of text, you return the column identifying the unique match id. When this case statement is inside the count function, it counts every ID returned by this case statement. You then add a second case statement for the away team and group the query by the season. When counting information in a case statement, you can return anything you'd like. A number, a string of text, or any column in the table. SQL is counting the number of rows returned by the case statement. Similarly, you can use the sum function to calculate a total of any value. Let's say we're interested in the number of home and away goals that Liverpool scored in each season. This is fairly simple to set up. If the home team ID is Liverpool's return the home goal value. The else clause is assumed to be null, so the query returns the total home goals scored by Liverpool in each season. You can also use the average function with case in two key ways. First, you can calculate an average of data. You can do this using the case in the exact same way you just used the sum function. Just change out the sum for average in this query and instead you get the average goals Liverpool scored in each season. You can make the results easier to read by using round. Round takes two a numerical value and the number of decimal points. To round the value to place it outside your aggregate case statement and include the number of decimal points at the end there. That's much easier to read. The second key application of case with average is in the calculation of percentages. This requires a specific structure in order for your calculation to be accurate. The question we're answering here is what percentage of Liverpool's games did they win in each season. The first component of this case statement is a when clause identifying what you're calculating a percentage of in this case, how many games did they win? This is tested in the same way as the previous slides, and your then clause returns a 1. The second component identifies Liverpool's games that they lost and returns a value 0. All other matches ties games not involving Liverpool are excluded as nulls. Here are the results of this query, and here's the rounded, more readable version of the results. Now it's your turn to practice creating case statements with aggregate functions.
