Calculations vs Aggregations

A calculation works within one row and returns a value for every row; an aggregation works across many rows and returns one value for all of them.

Apprentice · T-SQL · SQL · Updated

After this lesson you can

  • Tell a calculation (one value per row) from an aggregation (one value for many rows)

Key takeaways

  • A calculation such as UnitPrice * Quantity uses the values in one row and gives an answer for every row.
  • An aggregation such as AVG(Quantity) works across many rows and returns one row, however many rows went in.
  • COUNT, SUM, AVG, MIN and MAX are aggregate functions.
  • The two work together: SUM(UnitPrice * Quantity) works out each line's total, then adds them into one grand total.
  • Before you write the query, ask whether you want an answer for every row or one answer about all of them.

There are two kinds of arithmetic in a query, and they give very different results.

A calculation: one answer per row

A calculation works with the values in one row. It can't see any other row. This query works out each order line's total, so every row in OrderDetails gets its own answer:

SELECT TOP (4) OrderId, ProductId, UnitPrice * Quantity AS LineTotal
FROM OrderDetails
ORDER BY OrderId, ProductId;
OrderId ProductId LineTotal
10248 11 168.00
10248 42 98.00
10248 72 174.00
10249 14 167.40

Without the TOP, it returns all 2,155 rows of OrderDetails, each with its own line total.

An aggregation: one answer for many rows

An aggregation works across rows: it takes a column from many rows and boils it down to one value. This query asks for the average quantity on an order line, across every line of every order:

SELECT AVG(Quantity) AS AverageQuantity
FROM OrderDetails;
AverageQuantity
23

One row, however many rows went in. AVG is an aggregate function, and so are COUNT, SUM, MIN and MAX, the subject of the next lesson.

Side by side

Calculation Aggregation
Works with Values in the same row One column, across many rows
Rows in the result One per row One for all of them (or one per group, with GROUP BY)
Example UnitPrice * Quantity AVG(Quantity)
Answers "What's this line's total?" "What's the average line quantity?"

They can work together: SUM(UnitPrice * Quantity) calculates each line's total, then aggregates them into one grand total.

Check yourself

1 question to try on the lesson page.

So what

Ask yourself whether you want an answer for every row, or one answer about all of them. The first is a calculation; the second is an aggregation, and it's how databases turn thousands of rows into a single number a manager can use.

Key terms

Cheat sheets