Search⌘ K
Home/
Courses/
Practical Data Analysis with SQL

Conditional Aggregates

Learn how to apply conditions on aggregate expressions to produce advanced reports and pivot tables.

We'll cover the following...

Overview

When analyzing a table using aggregates, it is sometimes necessary to apply some filtering to the data. For example, say we want to count the number of managers and non-managers in a single query:

PostgreSQL
SELECT
  (SELECT COUNT(*) FROM emp WHERE role = 'Manager') AS managers,
  (SELECT COUNT(*) FROM emp WHERE role != 'Manager') AS non_managers
;

To include the counts of managers and non-managers in the same query, we executed two separate queries and inlined the results in a SELECT statement. The main downside to this approach is that it forces the database to scan the table multiple times. There is a better way!

Conditional aggregates with CASE

There is a certain way in which aggregate functions handle missing values. Functions like COUNT ignore NULL ...