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

By

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.

Do it: one total for all of today's order lines

The manager doesn't want a total for every order line. She wants one number: what all of today's order lines are worth together.

The OrderDetails table (the Tables tab shows the SQL that makes it) has these rows, and each line's total is its UnitPrice times its Quantity:

OrderId ProductId UnitPrice Quantity The line's total
10248 11 14.00 12 168.00
10248 42 9.80 10 98.00
10248 72 34.80 5 174.00
10249 14 18.60 9 167.40
10249 51 42.40 40 1,696.00
All together 2,303.40

Your query should give exactly this one row:

TotalValue
2303.4

Your job: write one SELECT query that gives the value of all the order lines together.

  • Show one column, named TotalValue.
  • Work out each line's total, UnitPrice times Quantity, and add them all up.
  • The answer is one row, whatever the number of lines.

Run the starter first: it gives five rows, one for each line. That's a calculation; the manager wants an aggregation.

Practiced live on the lesson page: write the SQL right there and check it.

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