Calculate a Dashboard Completion Rate Without Averaging Percentages

How does a ratio of sums weight project completion correctly?

A SQL dashboard task completion rate weights projects correctly by dividing summed completed tasks by summed total tasks, rather than averaging project percentages.

completion_rate = SUM(completed_tasks) / SUM(total_tasks)

A 90-task project contributes nine times as much to the denominator as a 10-task project. No separate stored weight is necessary.

The metric answers, “Of all included tasks, what share are complete?” It measures task counts, not effort: a ten-minute task and a two-day task each count as one. Label the dashboard accordingly.

Why does dividing SUM(completed_tasks) by SUM(total_tasks) weight team completion correctly?

Dividing SUM(completed_tasks) by SUM(total_tasks) weights team completion by task count because each project’s contribution grows with its number of tasks.

The query reads project_counts, then GROUP BY team collects each team’s project rows into one reporting group rather than calculating a single company-wide result. Within each group, SUM(completed_tasks) builds the completed-task numerator, while SUM(total_tasks) builds the full task population. Summing counts before division answers a task-level question rather than giving every project equal influence.

Multiplying the numerator by 1.0 makes fractional arithmetic explicit, although DuckDB’s / already supports fractional division for integers. NULLIF compares the summed denominator with zero and returns NULL when they match, representing an undefined rate for a team with no tasks.

Division then produces the completion fraction: Moon Badger has 39 / 100 = 0.39, and Waffle Comet has 20 / 25 = 0.80. Finally, AS completion_rate names the calculated column while leaving percentage presentation to the dashboard.

How do you set up project counts and calculate team completion in DuckDB?

Calculate team completion in DuckDB from one row per non-overlapping project, using consistent task definitions and a ratio-of-sums query.

1. Define which tasks belong in the dashboard

Choose a reporting population and cutoff, such as tasks in active projects at the same reporting time. Define completed statuses consistently.

Decide whether canceled tasks belong in the denominator. If excluded, remove them from every project’s counts consistently. Resolve these definitions before aggregation.

2. Keep exactly one row per non-overlapping project

Each included task must belong to only one included project, and each project must belong to one team for this calculation. Keep project_id to detect duplicates, although aggregation needs only team, completed_tasks, and total_tasks.

Use this fictional sample input:

project_id	team	completed_tasks	total_tasks
MB-101	Team Moon Badger	9	10
MB-102	Team Moon Badger	30	90
WC-201	Team Waffle Comet	18	20
WC-202	Team Waffle Comet	2	5
PD-301	Team Paper Dragon	0	0

Team Paper Dragon has a project record but no tasks.

3. Create the sample table in DuckDB

Create the sample in a scratch database or schema:

CREATE TABLE project_counts (
    project_id VARCHAR,
    team VARCHAR,
    completed_tasks BIGINT,
    total_tasks BIGINT
);

INSERT INTO project_counts (
    project_id,
    team,
    completed_tasks,
    total_tasks
)
VALUES
    ('MB-101', 'Team Moon Badger', 9, 10),
    ('MB-102', 'Team Moon Badger', 30, 90),
    ('WC-201', 'Team Waffle Comet', 18, 20),
    ('WC-202', 'Team Waffle Comet', 2, 5),
    ('PD-301', 'Team Paper Dragon', 0, 0);

Do not run this setup against an existing project_counts table. Use existing data only after confirming its grain and count definitions match these requirements.

4. Run the ratio-of-sums query

Aggregate completed and total task counts separately:

SELECT team, SUM(completed_tasks) * 1.0 / NULLIF(SUM(total_tasks), 0) AS completion_rate FROM project_counts GROUP BY team;

GROUP BY team returns one row per team. Multiplying by 1.0 makes fractional calculation explicit; DuckDB’s / already supports fractional division with integer operands, but this expression prevents integer-division surprises in many other SQL environments.

NULLIF(SUM(total_tasks), 0) converts a zero denominator to NULL, making the rate undefined rather than an invalid division result.

5. Compare with the expected output

The expected values for the sample are:

team	completion_rate
Team Moon Badger	0.39
Team Waffle Comet	0.80
Team Paper Dragon	NULL

Without ORDER BY, row order is not guaranteed. Decimal presentation can vary: 0.8 and 0.80 mean the same value. The defined percentage rates are 39% and 80%.

Why is Team Moon Badger’s completion rate 39% rather than 61.67%?

Team Moon Badger’s task completion rate is 39% because its projects contain 39 completed tasks out of 100 total tasks.

The numerator is 9 + 30 = 39; the denominator is 10 + 90 = 100. Therefore, 39 / 100 = 0.39.

Project MB-101: 9 / 10 = 90%
Project MB-102: 30 / 90 = approximately 33.33%
Unweighted average: approximately 61.67%
Correct task completion rate: 39%

The unweighted average describes the average project’s completion percentage, not the completed share of all tasks.

Team Waffle Comet has 20 / 25 = 0.80, or 80% completion. Its project rates are 90% and 40%, whose simple average is 65%. Both calculations describe valid but different metrics.

How should the dashboard format and combine SQL completion rates?

Store SQL completion rates as decimal fractions and apply percentage formatting only in the dashboard.

• Format 0.39 as 39%; multiplying by 100 before percentage formatting can produce 3900%.
• If the dashboard expects percentage points, document that contract and convert once.
• Display NULL as “N/A” or “No tasks,” not 0%.
• For company-wide rates, retain completed and total counts and recalculate their ratio instead of averaging team rates.

Apply filters consistently to numerator and denominator. Completed-this-week tasks divided by all-time tasks does not measure completion across a fixed task population; the label must reflect the actual scope.

Which source and aggregation failures distort the completion query?

SQL completion rates become misleading when dashboards average rates, joins duplicate projects, counts are missing or invalid, or undefined rates become zero.

Failure: the dashboard averages project rates again

Symptom: Team Moon Badger displays approximately 61.67% instead of 39%, or regrouping teams changes an overall card unexpectedly.

Exact fix: Remove percentage averaging and calculate SUM(completed_tasks) * 1.0 / NULLIF(SUM(total_tasks), 0) at the final reporting level. Supply underlying counts if the dashboard receives only team percentages.

Failure: a join duplicates project rows

Symptom: Adding assignees or labels increases totals and may shift the rate.

Exact fix: Restore one row per non-overlapping project before aggregation. Collapse duplicated project records to one authoritative count pair, or use an EXISTS filter when the join only selects projects.

Detect repeated identifiers with:

SELECT project_id, COUNT(*) AS row_count
FROM project_counts
GROUP BY project_id
HAVING COUNT(*) > 1;

Do not use SUM(DISTINCT completed_tasks): different projects can legitimately have identical counts.

Failure: missing counts are silently skipped

Symptom: A plausible rate appears despite unknown project counts because SQL aggregates ignore NULL inputs.

Exact fix: Identify incomplete records:

SELECT project_id, team, completed_tasks, total_tasks
FROM project_counts
WHERE completed_tasks IS NULL
   OR total_tasks IS NULL;

Populate counts from the source before publishing. Replace missing counts with zero only when the source definition explicitly says missing means zero; unknown completion is not zero completion.

Failure: invalid counts produce impossible percentages

Symptom: Rates exceed 100%, become negative, or appear believable because invalid projects offset each other.

Exact fix: Identify invalid rows and correct counts upstream:

SELECT project_id, team, completed_tasks, total_tasks
FROM project_counts
WHERE completed_tasks < 0
   OR total_tasks < 0
   OR completed_tasks > total_tasks;

Do not cap the final rate at 100%; capping conceals source errors.

Failure: an empty team is presented as unfinished work

Symptom: Team Paper Dragon displays 0%, implying unfinished tasks exist.

Exact fix: Preserve the query’s NULL, configure the dashboard to show “No tasks,” and remove downstream replacements that turn undefined rates into zero.

When should you avoid this project-count completion query?

Avoid this project-count query unchanged when projects overlap, snapshots repeat tasks, or the intended metric measures effort or average project completion.

Condition	Required change
Tasks appear in multiple projects	Start from task-level records and establish one included record per task at the reporting level.
Daily snapshots repeat project counts	Select the latest valid snapshot per project for current state; group by snapshot date and team for history.
The question concerns effort	Divide completed effort by total planned effort using consistent units and a distinct label.
The question concerns the average project	An unweighted average may fit; label it “Average project completion” and define treatment of zero-task projects.