Conditional Aggregates
Lesson 1 of 1
  1. 1
    Aggregate Functions Revisited
    Aggregate functions compute a single result from a set of multiple input values. You can think of aggregate data as data collected from multiple rows at a time. In this lesson, we’ll continue l…
    Start
  2. 2
    NULL
    While working with databases, it’s common to have empty or unknown “cells” in data tables. For example, in our first flights table, we keep track of when flights arrive in a column called arr_time:…
    Start
  3. 3
    CASE WHEN
    Almost every programming language has a way to represent “if, then, else”, or conditional logic. In SQL, we represent this logic with the CASE statement, as follows: SELECT CASE WHEN elevation …
    Start
  4. 4
    COUNT(CASE WHEN )
    Sometimes you want to look at an entire result set, but want to implement conditions on certain aggregates. For instance, maybe you want to identify the total amount of airports as well as the to…
    Start
  5. 5
    SUM(CASE WHEN )
    We can do that same thing for other aggregates like SUM(). For instance, if we wanted to sum the total flight distance and compare that to the sum of flight distance from a particular airline (in t…
    Start
  6. 6
    Combining aggregates
    Oftentimes we’d like to combine aggregates, to create percentages or ratios. In the instance of the last query, we might want to find out the percent of flight distance that is from United by ori…
    Start
  7. 7
    Combining aggregates II
    Modify the previous elevation example to find the percentage of high elevation airports (elevation >= 2000) by state.
    Start
  8. 8
    Generalizations
    Congratulations! You just learned about Conditional Aggregates in SQL. What can we generalize so far? * Conditional Aggregates are aggregate functions the compute a result set based on a give…
    Start