Aggregation Functions

Part of the Fundamentals section of Coddy's SQL journey — lesson 68 of 72.

Aggregation functions are used to calculate the AVG() or MAX() or any other aggregation function up until the current row.
For example, we could calculate the maximum revenue we got until each ending period:

month	revenue	region
4	40	East
5	20	East
6	60	West
7	55	West
8	61	East

SELECT month, revenue, MAX(revenue) OVER (ORDER BY month ASC) as max_revenue
from table1

This will return:

month	revenue	max_revenue
4	40	40
5	20	40
6	60	60
7	55	60
8	61	61

For months 4 and 5 the maximum revenue is 40, for months 5 and 6 the revenue is 60 and for months 8, it is 61. This is because when it finds a new bigger revenue, it drops the old one and uses the biggest so far.

For AVG() function it will look like this:

SELECT month, revenue, AVG(revenue) OVER (ORDER BY month ASC) as avg_revenue
from table1

month	revenue	avg_revenue
4	40	40
5	20	30
6	60	40
7	55	43.75
8	61	47.2

We can also group our calculations by specific categories using PARTITION BY. For example:

SELECT month, revenue, region,
MAX(revenue) OVER (PARTITION BY region ORDER BY month ASC) as max_revenue
FROM table1

Would give us:

month	revenue	region	max_revenue
4	40	East	40
5	20	East	40
6	60	West	60
7	55	West	60
8	61	East	61

Now the maximum is calculated separately for each region. The East region and West region maintain their own running maximums independently.

Challenge

Easy

Available tables and columns:
books: book, page, words

For each book, display the book name, its total pages, the number of words on each page, and calculate:

The maximum number of words found on any page in that book
The average number of words per page for the entire book
The difference between the current page's words and the book's average words per page

Display the results ordered by book and page number.

Return the following columns:

book
page
words
max_words_in_book
avg_words_per_page
diff_from_avg

Cheat sheet

Window functions with aggregation calculate values across a set of rows related to the current row. Use OVER clause with ORDER BY for running calculations:

SELECT column, 
       MAX(column) OVER (ORDER BY column ASC) as running_max,
       AVG(column) OVER (ORDER BY column ASC) as running_avg
FROM table

Use PARTITION BY to group calculations by categories:

SELECT column, category,
       MAX(column) OVER (PARTITION BY category ORDER BY column ASC) as max_per_category
FROM table

This calculates the maximum separately for each category, maintaining independent running calculations.

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Fundamentals

3Specific Return Format

Null values Sort Results Part 1 Sort Results Part 2 Recap - Cyber Security Firm Limit number of records Recap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary Election Recap - Police Criminal Arrest Recap - Bar Beverage Container Recap - Engineer new columns

9Multiple tables

Basic Join Part 1 Basic Join Part 2 Recap - Join Self join Recap - Self Join Union Simplify queries, WITH keyword Recap - With Queries Recap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK Functions Recap - RANK & DENSE_RANK NTILE FunctionAggregation FunctionsROWS & RANGE Criterion