Supporting reference and preserved earlier practice. Open the current classroom lesson →

SUPPORTING SQL REFERENCE · LESSON 7

Lesson 7: Whole-table aggregates

Summarize data using aggregate functions like COUNT, MIN, MAX, AVG, and SUM with GROUP BY.

Practice this lesson · 3 exercises ↓Related class labs ↓

Practice database: Buildings & employees. Recovered teaching snapshot. Explore the live schema below before choosing your columns.

Read the explanation, then use the practice section below. Generic syntax and illustrative tables explain the idea; the exercise schema shows the exact names available in Buildings & employees.

SQL aggregate functions summarize rows into a result you can explain. This lesson uses the employees table: each row represents an employee, years_employed records tenure, and role or building can define a group. Start with a whole-table maximum, then compare roles and buildings. A grouped result is one row per group, not one row per employee.

Select query with aggregate functions over all rows

SELECT

AGG_FUNC(column_or_expression) AS aggregate_description, ...

FROM

mytable

WHERE

constraint_expression;

Without a specified grouping, each aggregate function is going to run on the whole set of result rows and return a single value. And like normal expressions, giving your aggregate functions an alias ensures that the results will be easier to read and process.

Common Aggregate Functions

Here are some common aggregate functions that we are going to use in our examples:

Function	Description
*COUNT(), COUNT(column)**	A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.
*MIN(column)*	Finds the smallest non-NULL value in the specified column for all rows in the group.
*MAX(column)*	Finds the largest non-NULL value in the specified column for all rows in the group.
*AVG(column)*	Finds the average numerical value in the specified column for all rows in the group.
*SUM(column)*	Finds the sum of all numerical values in the specified column for the rows in the group.

Grouped Aggregate Functions

In addition to aggregating across all the rows, you can instead apply the aggregate functions to individual groups of data within that group (ie. box office sales for Comedies vs Action movies).
This would then create as many results as there are unique groups defined as by the GROUP BY clause.

Select query with aggregate functions over groups

SELECT

AGG_FUNC(column_or_expression) AS aggregate_description, ...

FROM

mytable

WHERE

constraint_expression

GROUP BY

column;

The GROUP BY clause works by grouping rows that have the same value in the column specified.

Key Aggregate Concepts:

COUNT(*): Counts all rows, including those with NULL values
COUNT(column): Counts only non-NULL values in the specified column
GROUP BY: Creates separate groups for each unique value
Aliases: Use AS to give meaningful names to aggregate results
NULL handling: Most aggregates ignore NULL values (except COUNT(*))

Exercise

For this exercise, we are going to work with our Employees table. Notice how the rows in this table have shared data, which will give us an opportunity to use aggregate functions to summarize some high-level metrics about the teams. Go ahead and give it a shot.

Try a worked example

SELECT MAX(years_employed) as Max_years_employed
FROM employees;

Read the query, predict its result, then inspect the rows or schema it produces.

Open example in playground ↗

TRY IT YOURSELF

Lecture 6: Queries with Aggregates (Pt. 1)

Buildings & employees · Summarize and compare

Recovered teaching snapshot. Explore the live schema below before choosing your columns.

Opening your saved work…

YOUR LESSON DATABASE

Tables, keys & sample rows

0 tables · Updates after your query

PK = primary key · FK = declared foreign key. Expand a table to see its columns and the first five stored rows.

Loading schema…

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Run a query to explore. Check my answer checks the selected exercise.

Read-only practice · One statement at a time · Ctrl / ⌘ + Enter runs your query