1. Learn
    2. /
  2. Courses
    3. /
  3. Data Manipulation in SQL
  • 1

    We'll take the CASE

    Free

    In this chapter, you will learn how to use the CASE WHEN statement to create categorical variables, aggregate data into a single column with multiple filtering conditions, and calculate counts and percentages.

  • 2

    Short and Simple Subqueries

    In this chapter, you will learn about subqueries in the SELECT, FROM, and WHERE clauses. You will gain an understanding of when subqueries are necessary to construct your dataset and where to best include them in your queries.

  • 3

    Correlated Queries, Nested Queries, and Common Table Expressions

    In this chapter, you will learn how to use nested and correlated subqueries to extract more complex data from a relational database. You will also learn about common table expressions and how to best construct queries using multiple common table expressions.

  • 4

    Window Functions

    You will learn about window functions and how to pass aggregate functions along a dataset. You will also learn how to calculate running totals and partitioned averages.

Initializing

Exercise

COUNT and CASE WHEN with multiple conditions

In R or Python, you have the ability to calculate a SUM of logical values (i.e., TRUE/FALSE) directly. In SQL, you have to convert these values into 1 and 0 before calculating a sum. This can be done using a CASE statement.

There's one key difference when using SUM to aggregate logical values compared to using COUNT in the previous exercise --

Your goal here is to use the country and match table to determine the total number of matches won by the home team in each country during the 2012/2013, 2013/2014, and 2014/2015 seasons.

Instructions

100 XP
  • Create 3 CASE statements to "count" matches in the '2012/2013', '2013/2014', and '2014/2015' seasons, respectively.
  • Have each CASE statement return a 1 for every match you want to include, and a 0 for every match to exclude.
  • Wrap the CASE statement in a SUM to return the total matches played in each season.
  • Group the query by the country name alias.