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.

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.

Reference: DuckDB documentation. Work through the examples in SQL from Zero to Working.