SQL Nested Aggregates: Separate Group Totals From Their Average

The practice task

Find the average number of completed tasks per team from a fictional task log. First count each team, then calculate the average of those team totals.

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 task_log(team TEXT);
INSERT INTO task_log VALUES ('A'),('A'),('B'),('B'),('B'),('B'),('C'),('C'),('C');

SELECT ROUND(AVG(team_total),2) AS mean_tasks_per_team
FROM (SELECT team, COUNT(*) AS team_total FROM task_log GROUP BY team) AS totals;

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 inner grouped query produces three totals: A has two tasks, B has four and C has three. The outer AVG sees those three numbers and returns three. It does not average the original nine task rows.

Writing AVG(COUNT(*)) at the same aggregate level does not state these two rowsets correctly for this task. A derived table or CTE supplies the missing intermediate result and gives the inner total a useful name.

Teams with no task rows are absent from this fixture. If they must count as zero, start from a complete team directory and retain the missing teams before averaging. Otherwise the result measures teams represented in the task log, which may overstate activity across the full organization.

Mistakes to check

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

• Define the grain at both levels of aggregation.
• Decide whether zero-activity groups belong in the denominator.
• Do not confuse an average team total with an average task value.