Totals and Averages
Part of the SQL Foundations section of Coddy's PostgreSQL journey. Lesson 12 of 22.
SUM adds non-null values and AVG divides their total by their count. Unlike COUNT, these aggregates return NULL when no values remain. COALESCE can give a report a deliberate zero fallback.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT SUM(stock) AS units, ROUND(AVG(stock), 2) AS average_units FROM products;The six stocks total 28 and average about 4.67. AVG ignores missing values instead of treating them as zero. Rounding controls the returned number, not the underlying rows.
SUM and AVG ignore NULL; use COALESCE when an empty aggregate should display zero.
Challenge
MediumReturn inventory_total, the sum of price times stock across all products. Return zero if there are no products.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in SQL Foundations
3Summarize Rows
Count Rows and ValuesTotals and AveragesGroup by CategoryFilter Groups with HAVINGCategory Restock BudgetPractice on your own: PostgreSQL playground