COUNT, SUM, AVG and observed denominators

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

COUNT, SUM, AVG and observed denominators

Concept and contract

Report how many values were observed alongside a statistic computed from them.

An aggregate summarizes a population. COUNT(*) counts rows, COUNT(salary) counts non-null salary observations and AVG(salary) averages those observed values. The worked query intentionally returns all three alongside SUM so the reader can reconcile the denominator rather than trusting a single attractive number.

There are eight roles, seven known salary values and a sum of 590 teaching units. Their observed average is 84.29 after rounding to two decimal places. The known zero participates; the unknown observation does not silently become a zero.

Mechanism and worked example

COUNT(expression) ignores null expression values.

COUNT(expression) ignores null expression values. SUM and AVG also ignore missing inputs, while an empty input can produce NULL for those aggregates. COUNT returns zero for an empty population. These differences matter when joining a grouped report to companies that have no observations.

ROUND makes the displayed precision explicit but does not improve the source evidence. The fixture uses whole teaching units, and its average is illustrative. Real salaries need comparable periods, currencies and role populations before a numerical comparison becomes meaningful.

Complete worked example

sql

SELECT COUNT(*) AS roles, COUNT(salary) AS observed,
 SUM(salary) AS total, ROUND(AVG(salary),2) AS mean
FROM roles;

Reasoning: The mean uses seven observed values, including zero.

Counterexamples and common mistakes

The counterexample averages COALESCE(salary,0).

The counterexample averages COALESCE(salary,0). It produces 73.75, using eight numeric inputs by turning one missing observation into an invented zero. That is a different statistic from the observed-value average, even though both queries are syntactically valid.

Another mistake uses COUNT(*) as the denominator while summing only known values and labels the result average observed salary. A report should state both its population and its observation coverage. A high row count does not guarantee good measurement coverage.

Counterexample: a plausible but wrong query

sql

SELECT COUNT(*) AS roles, COUNT(salary) AS observed,
 SUM(salary) AS total, ROUND(AVG(COALESCE(salary,0)),2) AS mean
FROM roles;

Reasoning: An invented zero changes the denominator and the result.

Independent practice: small query

Compute the same four measures for active roles only: total roles, known salary observations, salary sum and rounded observed average.

Compute the same four measures for active roles only: total roles, known salary observations, salary sum and rounded observed average. Predict six roles, five known values, a sum of 420 and an average of 84. This is a filter change, not a new aggregation mechanism.

Apply active status before aggregation. Do not carry over the complete-population denominator eight. Derive the expected sum from the five known active records by hand so your acceptance result is independent of the query implementation.

Step 1: List the six active identities. Step 2: Sum only their five known values and reconcile the denominator.

Practice starter: solve before reading the solution

sql

SELECT COUNT(*), COUNT(salary), SUM(salary), ROUND(AVG(salary),2)
FROM roles WHERE /* active population */;

Reasoning: Change the population before computing all four measures.

Hint 1: Role 3 is active but has unknown salary.
Hint 2: Role 8 has a known zero that still counts as observed.

Solution, tests and trade-offs

The solution returns 6, 5, 420 and 84.

The solution returns 6, 5, 420 and 84. The known zero remains part of the five observed values. Role 3 contributes to the total active population but not to the known-salary count or average, preserving the distinction established in the NULL lesson.

Check all four measures together: an incorrect filter can accidentally keep the same average while changing the underlying population. As an optional empty-population case, filter to a nonexistent country and inspect zero counts alongside null SUM and AVG results.

Practice solution and acceptance checks

sql

SELECT COUNT(*), COUNT(salary), SUM(salary), ROUND(AVG(salary),2)
FROM roles WHERE active = 1;

Reasoning: The active result is total 6, observed 5, sum 420 and mean 84.

Interview reasoning and next step

In an interview, describe exactly what each count measures and which observations enter an average.

In an interview, describe exactly what each count measures and which observations enter an average. Avoid claiming that every null should be ignored or replaced universally. Missing-data policy belongs to the business question, and the query should make that policy visible.

The next lesson divides a population into groups and filters those groups with HAVING. The denominator reasoning remains the same inside each group, so trace the source rows before discussing the final grouped summary.

Optional stretch — after the core task works: Verify the empty-population output and explain why SUM and COUNT differ.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Report how many values were observed alongside a statistic computed from them.