SQL Average: Check the Denominator Before Comparing Rates

The practice task

Compare the average of two fictional team resolution rates with the overall resolution rate. One team handled 100 enquiries and resolved 90; the other handled one and resolved none.

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 teams(name TEXT, enquiries INTEGER, resolved INTEGER);
INSERT INTO teams VALUES ('North',100,90),('South',1,0);

SELECT ROUND(AVG(100.0 * resolved / enquiries),2) AS mean_team_pct,
 ROUND(100.0 * SUM(resolved) / SUM(enquiries),2) AS overall_pct
FROM teams;

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 two individual rates are 90 percent and zero percent. Their unweighted mean is 45 percent. Across all enquiries, 90 of 101 were resolved, producing an overall rate of approximately 89.11 percent.

Neither result is automatically wrong. The mean treats the two teams equally; the combined rate treats each enquiry equally. A report must state which unit receives equal weight so a reader does not mistake one measure for the other.

The fixture uses positive denominators. A zero or missing enquiry count needs its own policy before calculating team rates. Do not silently replace an unknown rate with zero, since that changes the mean and implies evidence the dataset may not contain.

Mistakes to check

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

• State whether the unit being averaged is a team, person, event or period.
• Do not round each intermediate rate more than the task requires.
• Keep the combined numerator and denominator available for a weighted calculation.