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
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_pricetimesquantity, 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.