Lesson 4.2
SQL SUM and AVG Aggregates
SUM and AVG turn numeric columns into totals and averages.
SUM adds values. AVG averages them. They only make sense for numeric expressions.
Aggregates can use calculated expressions too. For each order item, the line total is quantity * unit_price, and SQL can sum or average that expression directly.
Check the unit before averaging
An average is only useful when every row represents the same kind of thing. Here, each row is one order line, so avg(quantity * unit_price) means average line total. It does not mean average order total unless you group line items by order first.
Pattern
select sum(number_column) as total_value,
avg(number_column) as average_value
from table_nameSchema · Garden ShopTable · order_items5 columns · 48 rows
Table · order_items
5 columns · 48 rowsOne row per line item within an order.
order_item_id intorder_id intproduct_id intquantity intunit_price decimal
Your task
Return one summary row with total units sold,gross sales, and the average line totalacross all order items.
SQL Workbench
⌘↵ to run·
Expected answer
- Columns: units_sold, gross_sales, average_line_total.
- Rows: 1 summary row.
- Expected values: 87 units sold and 1129.00 gross sales.