COUNT, SUM, AVG

Learning Focus

Use this lesson to master COUNT, SUM, and AVG — the three most common aggregate functions in DuckDB.

Overview

Aggregate functions reduce multiple rows to a single value:

Function	Description
COUNT(*)	Count all rows
COUNT(col)	Count non-NULL values
COUNT(DISTINCT col)	Count unique non-NULL values
SUM(col)	Total sum of values
AVG(col)	Arithmetic mean

Examples

-- Total employees
SELECT COUNT(*) AS total_employees FROM employees;

-- Active employees only
SELECT COUNT(*) AS active_count FROM employees WHERE active = true;

-- Non-NULL hire_dates
SELECT COUNT(hire_date) AS hired_count FROM employees;

-- Unique departments
SELECT COUNT(DISTINCT department) AS dept_count FROM employees;

-- Total payroll
SELECT SUM(salary) AS total_payroll FROM employees;

-- Average salary
SELECT AVG(salary) AS avg_salary FROM employees;

-- Per-department aggregation
SELECT
    department,
    COUNT(*)        AS headcount,
    SUM(salary)     AS total_salary,
    AVG(salary)     AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

FILTER Clause (DuckDB/Standard SQL)

-- Count with inline condition (no subquery needed)
SELECT
    COUNT(*) FILTER (WHERE active = true)  AS active_count,
    COUNT(*) FILTER (WHERE active = false) AS inactive_count,
    COUNT(*)                               AS total
FROM employees;

-- Conditional sum
SELECT
    SUM(salary) FILTER (WHERE department = 'Engineering') AS eng_payroll,
    SUM(salary) FILTER (WHERE department = 'Marketing')   AS mkt_payroll
FROM employees;

Nested Aggregations with Subqueries

-- Average of department averages
SELECT AVG(avg_salary) AS avg_of_avgs
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
);

Common Pitfalls

Pitfall	Consequence	Prevention
COUNT(col) vs COUNT(*) confusion	Misses NULLs	Use COUNT(*) for total rows, COUNT(col) for non-NULLs only
AVG on INTEGER columns	Truncated result	DuckDB returns DOUBLE — confirm with typeof(AVG(col))
Forgetting GROUP BY with aggregates	Error or wrong results	Any non-aggregated SELECT column must be in GROUP BY

Quick Reference

COUNT(*)
COUNT(col)
COUNT(DISTINCT col)
SUM(col)
AVG(col)
COUNT(*) FILTER (WHERE condition)
GROUP BY dept

What's Next

• Next: MIN and MAX - Find extreme values in your data.
• Module Overview - Return to this module index.