Data aggregation computes summaries such as counts, sums, minimums, maximums, averages, and standard deviations over rows or groups. It supports practical analysis and data-quality checks. section 4.6.1 section 10.4.5
A practical aggregation workflow can:
- Filter invalid rows before aggregation, such as taxi trips with negative distances. section 10.4.5
- Summarize one column with min, max, average, standard deviation, non-null count, total count, and null percentage. section 10.4.5
- Apply aggregate functions across multiple columns using DuckDB column selection and expressions. section 10.4.5
- Use HAVING after GROUP BY when filtering depends on an aggregate result. section 4.6.1
Filter daily energy totals
Group readings by system and day, calculate daily kWh with SUM(power), then retain only groups meeting the 900 kWh threshold with HAVING. section 4.6.1
Listing 4.21 Using the HAVING clause to filter rows based on aggregated values
SELECT system_id,
date_trunc('day', read_on) AS day,
round(sum(power) / 4 / 1000, 2) AS kWh,
FROM readings
GROUP BY ALL
HAVING kWh >= 900
ORDER BY kWh DESC;
Inspect the complete DuckDB query that groups readings, computes kWh, filters aggregated results, and orders the qualifying groups. section 4.6.1
