Use GROUP BY team in a SQLite query to return one summary row per team. Select both COUNT(*) and COUNT(minutes) to show the number of jobs and the number with recorded durations. AVG(minutes) uses only the duration values that are present, so the counts tell readers what the average covers.
Describe the rows before grouping
Imagine a repair shop table in which each row is one completed job. The team column identifies who finished it, and minutes holds a recorded duration or NULL if none was recorded. A question about total completed jobs needs all rows. A question about average observed duration needs only rows with a measured duration. The job table can answer both if the report does not confuse their denominators.
A grouping key defines which records appear together. Grouping by team gives one summary per team. Grouping by team and day gives one per pair. If the table stores multiple event rows per job, first establish whether each row should count or whether you need one row per job. Grouping early can hide duplicate rows instead of fixing them.
Return job counts and average duration together
This example uses synthetic data, an in-memory database and a single SQL summary.
import sqlite3
from contextlib import closing
with closing(sqlite3.connect(":memory:")) as db:
db.execute("CREATE TABLE jobs (team TEXT, minutes INTEGER)")
db.executemany("INSERT INTO jobs VALUES (?, ?)", [
("North", 10), ("North", None), ("South", 4), ("South", 8),
])
summary = db.execute("""
SELECT team, COUNT(*) AS jobs_recorded,
COUNT(minutes) AS jobs_measured, AVG(minutes) AS mean_minutes
FROM jobs
GROUP BY team
ORDER BY team
""").fetchall()
assert summary == [("North", 2, 1, 10.0), ("South", 2, 2, 6.0)]
North has two jobs in the table, but only one contributes to its mean. Reporting its mean without the measured count makes it look more comparable to South than the data supports. When a group exists but has no non-null measured values, its average is null. Python’s sqlite3 interface represents SQL null as None.
Filter rows with WHERE and groups with HAVING
A WHERE condition filters rows before grouping. Use it for selecting relevant records, such as completion dates within the requested reporting interval. A HAVING condition filters groups based on aggregate results, such as keeping only groups with at least ten measured jobs. Filtering on a measurement column before grouping can remove records from all measures, including the all-job count. Decide which columns must retain the original population.
Use placeholders in Python queries when passing values such as dates or team labels. A placeholder represents a value, so it does not select a column name or change SQL syntax. For user-selectable sort options, choose a query expression from a fixed set you control. Parameters help keep the SQL and input data separate.
Avoid accidental row duplication in joins
Adding a lookup table can multiply rows if the supposed lookup key is duplicated. Check the relationship before aggregating. A left join for showing groups without recorded jobs needs another distinction. Counting all joined rows may count the placeholder row created for an unmatched group. Count a non-null job identifier instead to calculate matched jobs.
Keep additional selected columns in the grouping definition or aggregate them deliberately. SQLite permits certain selected columns that are outside the grouping and aggregate expressions, but that does not generally provide a reliable representative value for a group. If you need the most recent job in each team, define a rule with a tie-breaker and implement that separately from the aggregate.
Watch the denominator when a filter removes groups. If the displayed summary lists only teams with at least ten measured jobs, its combined count no longer represents all teams. Keep a total for the complete population or label the displayed total as a subtotal. For a comparison with a prior period, verify whether a team that disappears was inactive or fell below the listing threshold.
Make report order and checks explicit
Set ORDER BY for a stable report and include a tie-breaker if groups share the same sort value. Test one team with jobs and no recorded minutes and another team with no matching jobs. The first team exists in the job table and its average is null. To display the second team, begin with a team list and join the jobs to it. To calculate a combined average, use the original duration totals and measured counts.