Aggregate Functions

Every query up to this point has returned individual rows - one line per movie, one line per review. An aggregate function works differently: it looks across multiple rows at once and collapses them down into a single summary value. "How many reviews does StreamHub have in total?" and "what's the average rating across every review for one movie?" are both aggregate questions - you don't care about any single row, you care about a number that describes the whole group.

The Five Core Aggregate Functions

Counting Rows: COUNT(*)

How many reviews has StreamHub collected in total, across every movie?

SELECT COUNT(*) AS total_reviews
FROM reviews;

Averaging a Single Movie's Ratings: AVG()

What's the average rating for one specific movie, say the one with id = 3?

SELECT AVG(rating) AS avg_rating
FROM reviews
WHERE movie_id = 3;

Notice the WHERE clause runs first, narrowing things down to just that movie's reviews - then AVG() summarizes whatever rows survive the filter. This order (filter first, then aggregate) is exactly why WHERE can't reference an aggregate result directly, a rule we'll unpack fully alongside HAVING in the next lesson.

Counting Unique Values: COUNT(DISTINCT column)

Plain COUNT(column) counts every non-null value, duplicates included. Adding DISTINCT counts only the unique values - useful for a question like "how many different users have left at least one review," where the same user might have reviewed several movies:

SELECT COUNT(DISTINCT user_id) AS reviewers
FROM reviews;

Without DISTINCT, COUNT(user_id) would return 11 - one for every review row - even though only 4 distinct people wrote them.

Multiple Aggregates in One Query

Aggregate functions aren't limited to one per query. A single SELECT can compute several summary values side by side, each scanning the same set of rows:

SELECT
  COUNT(*) AS total_reviews,
  AVG(rating) AS avg_rating,
  MIN(rating) AS lowest_rating,
  MAX(rating) AS highest_rating
FROM reviews;

One trip to the table, four different summary numbers - far cheaper than running four separate queries.

SUM() for Totals

SUM() adds up a numeric column. It's less common on reviews directly, but it's exactly what you'd reach for on a table tracking things like watch-time minutes or revenue. As a StreamHub-flavored example, total minutes across every movie in the catalog:

SELECT SUM(duration_minutes) AS total_catalog_minutes
FROM movies;

One Giant Summary Row - For Now

Every example on this page has produced exactly one row of output, no matter how many rows it scanned - because without any extra instruction, an aggregate function summarizes the entire result set at once. That's fine for "the average rating across all of StreamHub," but it can't answer "the average rating for each movie" - that question needs one summary row per movie, not one giant summary across everything. The next lesson, GROUP BY & HAVING, is entirely about that: splitting rows into buckets first, so every aggregate function here runs once per bucket instead of once overall.