Aggregate Functions & GROUP BY

Aggregate functions are used to compute a single result from multiple rows of data. In data analysis, these functions are very useful for condensing large amounts of information into a single meaningful number.

Available Aggregate Functions

DuckDB supports the following standard SQL aggregate functions:

Aggregate Function Usage Examples

Assume the grades table has the following data:

Counting rows

SELECT COUNT(*) AS total_rows
FROM grades;

The result:

Finding the highest and lowest scores

SELECT MAX(score) AS highest_score, MIN(score) AS lowest_score
FROM grades;

The result:

Calculating the average score

SELECT AVG(score) AS average_score
FROM grades;

The result:

Grouping Data with GROUP BY

The GROUP BY clause is used to group rows that share the same value in a given column, and then apply aggregate functions to each group.

Average score per student

SELECT student_id, AVG(score) AS average
FROM grades
GROUP BY student_id;

The result:

Total score per course

SELECT course, SUM(score) AS total_score
FROM grades
GROUP BY course;

The result:

Filtering Groups with HAVING

The HAVING clause is used to filter results after grouping (GROUP BY) has been performed. This is different from WHERE, which filters rows before grouping.

For example, only show students with an average score above 80:

SELECT student_id, AVG(score) AS average
FROM grades
GROUP BY student_id
HAVING AVG(score) > 80;

The result:

Differences Between WHERE and HAVING

Summary

In this chapter, we learned the aggregate functions (COUNT, SUM, AVG, MAX, MIN), how to group data with GROUP BY, and how to filter groups with HAVING. We also understood the difference between WHERE and HAVING.

In the next chapter, we'll wrap up this tutorial with a cheatsheet of commonly used DuckDB commands.