SQL GROUP BY

What & Why

Every aggregate function covered so far (COUNT, SUM, AVG, MIN, MAX) collapsed an entire table down to a single summary row. That's useful, but real questions are usually more specific: not "what's the average page view count across all sessions," but "what's the average page view count per device." GROUP BY is what makes that possible.

Here's the core idea: GROUP BY column_name takes every row in the result and sorts it into a bucket based on that column's value. All rows sharing the same value land in the same bucket. Then, any aggregate function in the SELECT list is computed separately for each bucket — not once for the whole table, but once per group. The final result has exactly one row per distinct value in the grouped column, no matter how many original rows fed into it.

This is a genuinely different way of thinking about a query than anything covered in the Beginner track. Until now, every query returned one row per row in the source table (possibly filtered, sorted, or limited) — the row count in, roughly, is the row count out. GROUP BY breaks that assumption entirely: seven session rows can become three device rows. Getting comfortable with that mental shift is really the whole point of this lesson.

See How It Works

SELECT
  device,
  ROUND(AVG(page_views), 2) AS avg_page_views
FROM growth.sessions
GROUP BY device
ORDER BY device;