Aggregate functions

So far you have retrieved individual rows. But the real power of SQL is turning thousands of rows into a single insight: "What is the average order value?" or "How many orders shipped last month?" Aggregate functions collapse many rows into summary values.

Aggregation: many rows to one answer

aggregates.sql
sql
-- Basic aggregate functions on order_items
SELECT
  COUNT(*) AS total_items,
  SUM(price) AS total_revenue,
  AVG(price) AS avg_price,
  MIN(price) AS cheapest_item,
  MAX(price) AS most_expensive_item
FROM order_items;

Five aggregate functions that answer five different business questions in a single query.

COUNT(*) counts all rows, including those with NULL values. COUNT(column_name) counts only rows where that column is NOT NULL. This is a key distinction when your data has missing values.

count_nulls.sql
sql
-- See the difference: COUNT(*) vs COUNT(column)
SELECT
  COUNT(*) AS total_reviews,
  COUNT(review_comment_message) AS reviews_with_comments
FROM order_reviews;

-- Many reviews have no comment (NULL)
-- COUNT(*) >> COUNT(review_comment_message)

This shows that many reviews lack a comment. COUNT(*) counts all 99k reviews. COUNT(review_comment_message) counts only those with actual text.

Fill in the blanks: Complete the aggregation query

Loading practice…

Matching exercise: Match the aggregate function

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Revenue summary

Loading practice…