SQL Aggregation

SQL aggregation summarizes a collection of rows. GROUP BY country produces a summary for each country value present in the input; SUM, COUNT, and AVG describe that group. Grouping by country and month changes the result grain to one row per country-month. Grouping by a month number alone combines the same month across years.

WHERE selects input rows before grouping. HAVING selects resulting groups using conditions such as COUNT(*) > 10. Filtering to paid orders before aggregation answers a different question from keeping groups whose total paid amount exceeds a threshold. GROUP BY does not guarantee output order; add ORDER BY for presentation.

COUNT(*) counts all rows in the group. COUNT(amount) and AVG(amount) exclude NULL amounts. With no input and no GROUP BY, COUNT returns zero while SUM and AVG return NULL. With ordinary GROUP BY and no input, there are no groups to return. A display default does not establish that unobserved activity was zero.

Averaging ratios is not the overall ratio

This one is worth arithmetic rather than a warning, because both numbers look correct and only one answers the usual question. Two stores:

Store	Conversions	Visits	Rate
A	1	10	10%
B	9	30	30%
Total	10	40	25%

Averaging the two rates gives (10% + 30%) / 2 = 20%. Dividing the totals gives 10 / 40 = 25%. Neither is a mistake in itself: the 20% is the mean of the store-level rates, weighting a store with 10 visits the same as one with 30, and the 25% is the rate for a visit drawn from the whole business. The error is writing AVG(rate) and describing the result as the overall conversion rate.

So compute a ratio from summed components rather than from other ratios, guarding the denominator and making the division non-integer — two integer sums divided give an integer in PostgreSQL and SQLite, so 10 / 40 is 0, not 0.25:

SELECT SUM(conversions) * 1.0 / NULLIF(SUM(visits), 0) AS conversion_rate
FROM store_daily;
-- 0.25 on SQLite 3.53.4 for the two stores above; without the * 1.0 it returns 0

That works only when the numerator and denominator are themselves addable across the rows you are combining, which is the condition to check first. Counts of events usually are. Distinct counts generally are not: daily unique visitors summed over a month counts a returning visitor several times, so the sum is not the month’s number of distinct visitors, and a rate built from such sums is not in general the month’s rate per distinct visitor, however the division is written. It can come out equal by coincidence — two days with visitors {A, B} and converter {A} give 2/4 and 1/2, both 50% — but the method is still not sound. Where a component is not addable, the ratio has to be recomputed from the underlying detail, or from a combinable representation such as a set of identifiers or a mergeable sketch, rather than assembled from stored counts.

If a different weighting is what you actually want — each store weighted equally, say — write it deliberately and label it that way, rather than letting AVG decide the weighting by accident.

For a share, decide whether the denominator includes every group or only those displayed. Two orders out of ten give 20%; removing other groups before calculating the total changes that fraction. Guard a zero denominator and label an undefined result explicitly. A matching total verifies one arithmetic relationship, not the source’s completeness or the validity of every record.

Further reading

• DuckDB Documentation, Aggregate Functions
• SQLite Documentation, Datatypes in SQLite
• Snowflake Documentation, Computing the Number of Distinct Values

See the worked examples in SQL from Zero to Working.