Lesson 6 of 13 · 20 min
Conditional aggregation
By the end of this lesson you can build a pivot-style summary with several conditional counts and sums per group, using FILTER and CASE, and compute rates such as a cancellation percentage correctly.
Suppose sales wants, per channel, the number of orders and the number of cancelled orders. A WHERE clause cannot do it: WHERE status = 'cancelled' would remove the other orders, and the total would be lost.
What you need is an aggregate that only looks at some of the rows of each group while the other aggregates still see all of them. That is conditional aggregation. PostgreSQL offers two ways to write it.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons