LEARN. PRACTICE. GROW.What are you counting?
COUNT(*) counts rows. COUNT(column) counts non-null values in that column. GROUP BY defines groups. A total is only useful when you know what each row represents before and after a JOIN.
Filter groups
HAVING filters aggregate results, such as teams with more than five incidents. WHERE applies to input rows. Also document the time window and time zone used in a metric to avoid incompatible comparisons.
Guided workplace application
In a fictional monthly close, two distinct orders of 100 euros should total 200 even though their amounts match. If each has two detail lines, summing the order total after the JOIN yields 400. SUM(DISTINCT amount) yields 100 because it distinguishes values rather than identities. Return to one row per eligible order before summing. Also define the period and time zone: adjacent start-inclusive, end-exclusive intervals avoid counting a boundary twice. For an empty population, SUM returns NULL; convert it to zero only when the metric contract defines that convention. COUNT(*) and COUNT(column) answer different questions. Record the unit, population, and filters alongside the indicator to support reconciliation.
SELECT team_id, COUNT(*) AS total
FROM incidents
WHERE severity = 1
GROUP BY team_id
HAVING COUNT(*) > 5A monthly indicator counts occurrences, not update rows for the same incident. Choose the right entity and key before publishing the number.
Common pitfalls
Fixing duplication with SUM DISTINCT; omitting the time zone or including a boundary twice.
Related topics: Changes that preserve rules · Indexes and execution plans
A metric starts by defining the unit being counted.
Reference: PostgreSQL 18: Aggregate functions · PostgreSQL 18 reference semantics; DR SQL 2026.2