Row-level vs aggregate behavior

Concepts covered: Counting

There are 2 families of expression in a SELECT list, and the family decides how many rows come back. A row-level expression reads 1 row and returns 1 value for that row: amount + 5, CAST(amount AS DECIMAL(10,2)), a CASE expression. Feed it 6 rows and it hands back 6 rows. An aggregate reads a set of rows and returns 1 value for the whole set: SUM(amount), COUNT(*), MAX(amount). Feed it the same 6 rows and it hands back 1 row.

Row-level expression

• Reads 1 row, returns 1 value
• Output row count equals input row count
• Runs during SELECT, after WHERE has filtered
• amount + 5, CAST, CASE

Aggregate function

• Reads a set of rows, returns 1 value
• Output row count equals the number of groups
• Runs after GROUP BY and feeds HAVING
• SUM, COUNT, AVG, MIN, MAX

Where the collapse happens

GROUP BY sets how many rows survive: 1 per distinct combination of the grouped columns, which is why 6 orders from 3 customers group into 3 rows. With no GROUP BY, a single aggregate in the SELECT list collapses the whole table into exactly 1 row, even an empty table, where COUNT(*) returns 0 on its 1 row. If a query has neither a GROUP BY nor an aggregate, no rows collapse and the row count is whatever WHERE let through. The 3 tabs below run on the same 6 orders. Only the first keeps all 6, and the other 2 come back with 1 row and 3 rows.

Row-level

amount + 5 runs on each of the 6 orders, so 6 rows come back, 1 per order.

SELECT
  order_id,
  amount,
  amount + 5 AS amount_with_fee
FROM orders

Aggregate

COUNT and SUM fold all 6 orders into a single row: 6 orders worth 690.

SELECT
  COUNT(*) AS order_count,
  SUM(amount) AS total
FROM orders

Grouped

GROUP BY customer_id folds the 6 orders into 3 rows, 1 per customer.

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total
FROM orders
GROUP BY customer_id

Mixing the families

Once a query has an aggregate or a GROUP BY, every column in the SELECT list is either grouped or wrapped in an aggregate. The query below asks for SUM(amount) per customer but also selects the bare amount column next to customer_id. Customer C1 has 3 amounts, and the result row has room for 1 value. Nothing tells the engine whether to show 120 or 60 or 90, so it rejects the query before reading a row.

SELECT
  customer_id,
  amount,
  SUM(amount) AS total
FROM orders
GROUP BY customer_id

The engine will not pick a value for you, so you pick. If the question is about the group, wrap the column in the aggregate that answers it: MIN(amount) for the smallest order, MAX for the largest, COUNT(DISTINCT amount) for how many different sizes. The animation below faces the same choice with 2 managers per region and resolves it with MIN, which picks the alphabetically first name, and COUNT, which reports how many names were folded into it.

SELECT
  region,
  MIN(manager) AS first_manager,
  COUNT(*) AS managers
FROM region_managers
GROUP BY region

Row-level inside aggregate

The 2 families nest. An aggregate can wrap a row-level expression: SUM(amount + 5) adds 5 to each of the 6 amounts first, then folds the 6 results into 720. A row-level expression can wrap aggregates, SUM(amount) / COUNT(*), because by then each side is a single value per group. What fails is a bare reference to a column that the collapse removed, which is the rejected query above.

How many rows does this query return on the 6-row orders table above?

SELECT
  customer_id,
  MAX(amount) AS biggest
FROM orders
WHERE amount > 50
GROUP BY customer_id

• 5 rows, 1 per order that passes the WHERE filter
• 3 rows, 1 per customer that still has an order
• 1 row, because MAX is an aggregate function
• 6 rows, since WHERE runs after the aggregate

Writing a grouped query runs the same reasoning in reverse. Start from the row the result needs: 1 row per product means the product column goes in GROUP BY and appears in SELECT as it is. Every other column has to arrive through an aggregate, so a number of sales becomes COUNT(*) and the largest single sale becomes MAX wrapped around the amount column.

About this lesson

This section is part of the Aggregating: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.