Calculations vs Aggregations

The difference between a calculation, which works on the values in one row, and an aggregate function, which turns many rows into one value.

Apprentice · SQL · MySQL · Updated

By

Aggregate functions are built-in ANSI SQL functions to help us aggregate (or calculate) data from multiple rows of data into a single value.

Arithmetic Calculation

A calculated value is an arithmetic expression which calculates two or more values in the same row. Arithmetic expressions cannot access values outside the current row. The following query returns ALL rows in the order_details table with the line_total calculated for each row.

SELECT unit_price * quantity as line_total
FROM order_details;

Aggregations

An aggregate function is a calculation that is based on values in all rows. The following query returns a SINGLE row that calculates the average quantity per line item for ALL orders placed.

SELECT AVG(quantity) AS average_quantity
FROM order_details;

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 order_details table (the Tables tab shows the SQL that makes it) has these rows, and each line's total is its unit_price times its quantity:

order_id product_id unit_price 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:

total_value
2303.4

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

  • Show one column, named total_value.
  • Work out each line's total, unit_price 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.

Cheat sheets