SQL Aggregate Functions: Compare What Each Summary Measures

The practice task

Inspect four fictional basket quantities: three units, seven units, zero units and one missing quantity. Produce a compact summary that makes the total, known observations and full row count visible together.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE baskets(quantity INTEGER);
INSERT INTO baskets VALUES (3),(7),(0),(NULL);

SELECT COUNT(*) AS rows_seen, COUNT(quantity) AS known,
 SUM(quantity) AS total, MIN(quantity) AS smallest,
 MAX(quantity) AS largest, ROUND(AVG(quantity),2) AS mean
FROM baskets;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The total is ten units across three known quantities. The minimum is zero, not three, because zero is a recorded value. The average is based on the three present quantities rather than all four rows.

A single total can hide data completeness. Reporting rows_seen and known alongside it helps a reader notice that one observation is missing. It does not justify treating the unknown basket as empty or estimating what it contained.

Choose the level of summary before adding GROUP BY. This query has one summary for the entire table. Grouping by a category would create a separate set of calculations for each category. Selecting an unrelated label next to an ungrouped aggregate does not establish that the label belongs to an extreme or represents the whole set.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Keep unknown values distinct from measured zeros.
• Name totals and averages with the unit they measure.
• Avoid selecting arbitrary non-grouped labels alongside summary values.