Querying
Aggregate Functions
Aggregate functions compute a single result value from a set of input rows.
Overview
Aggregate functions compute a single result value from a set of input rows. Standard SQL defines COUNT, SUM, AVG, MIN, and MAX. They are most often used with GROUP BY to produce one row per group, or without it to collapse an entire result set into one row. Aggregate functions differ from scalar functions because they operate on row sets rather than on individual values. COUNT(*) counts rows, while COUNT(expr) counts non-null values of that expression. SUM and AVG ignore nulls; MIN and MAX also ignore nulls. AVG is defined as the sum of non-null values divided by the count of non-null values, which can differ from dividing by the total row count. SQL-86 introduced the core aggregate functions, and later standards added EVERY, ANY, SOME, STDDEV_POP, VAR_POP, and ordered-set aggregates such as PERCENTILE_CONT. Vendor support varies: MySQL, PostgreSQL, SQL Server, and Oracle all implement the core five, but extended aggregates and exact null handling can differ. Aggregate results are often filtered with HAVING, not WHERE, because WHERE runs before grouping.
Syntax and usage
The general form is aggregate_function([DISTINCT | ALL] expression). ALL is the default and includes every non-null value; DISTINCT removes duplicate values before aggregation. For example, SELECT COUNT(*) FROM orders returns the number of rows, while SELECT COUNT(DISTINCT customer_id) FROM orders returns the number of distinct non-null customers. SUM(amount), AVG(amount), MIN(amount), and MAX(amount) follow the same pattern. Aggregates appear in the SELECT list and, when grouping is used, in the GROUP BY or HAVING clauses. A query may combine several aggregates: SELECT dept_id, COUNT(*), AVG(salary) FROM employees GROUP BY dept_id. Without GROUP BY, an aggregate over the whole table returns one row. HAVING filters groups after aggregation, so SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5 is valid. WHERE cannot contain aggregates because it is evaluated before grouping. Standard SQL also allows aggregates in ORDER BY, for example ORDER BY COUNT(*) DESC. Some vendors allow aggregates in SELECT with non-aggregated columns only if those columns are functionally dependent on the group keys; others reject such queries.
How it works
Conceptually, the database forms groups from the input rows according to the GROUP BY keys, then evaluates each aggregate over the rows in each group. With no GROUP BY, all rows form one group. Most aggregates ignore null input values. COUNT(*) is the exception: it counts rows, including rows where every column is null. COUNT(expr) counts only rows where expr is not null. SUM, AVG, MIN, and MAX skip nulls; if every input value is null, SUM, AVG, MIN, and MAX return null, while COUNT returns zero. AVG is computed as SUM(expr) / COUNT(expr), not as the sum divided by the total number of rows. DISTINCT is applied to the expression values before the aggregate is computed, so SUM(DISTINCT x) and SUM(ALL x) can differ. The SQL standard does not guarantee the order in which rows are processed, and aggregate results are generally order-independent except for order-sensitive aggregates such as ARRAY_AGG or STRING_AGG with an ORDER BY clause. Physical execution may use hash aggregation, sorting, or index scans, but the logical result is defined by the standard, not by the plan.
Examples
Basic counts and sums: SELECT COUNT(*) AS row_count, COUNT(email) AS email_count FROM users. Grouped aggregation: SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id. Filtering groups with HAVING: SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5. Distinct aggregation: SELECT COUNT(DISTINCT customer_id) FROM orders. Multiple aggregates in one query: SELECT MIN(price), MAX(price), AVG(price) FROM products. Aggregates with WHERE: SELECT SUM(amount) FROM orders WHERE status = 'paid'. Aggregates in ORDER BY: SELECT dept_id, COUNT(*) AS c FROM employees GROUP BY dept_id ORDER BY c DESC. A common mistake is mixing an aggregate with a non-grouped column: SELECT dept_id, COUNT(*) FROM employees is invalid in standard SQL because dept_id is neither aggregated nor grouped. The correct form adds GROUP BY dept_id. Aggregates can also be used in subqueries, for example SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees).
Common pitfalls
The most frequent error is selecting a non-aggregated column without GROUP BY, such as SELECT dept_id, COUNT(*) FROM employees. Standard SQL rejects this because dept_id is neither grouped nor aggregated. Another pitfall is using WHERE to filter aggregate results: WHERE COUNT(*) > 5 is invalid; use HAVING instead. Null handling surprises many users. COUNT(*) counts rows, but COUNT(column) skips nulls, so the two can return different numbers. SUM, AVG, MIN, and MAX return null when all inputs are null, which can propagate into arithmetic expressions. AVG divides by the count of non-null values, not by the row count, so it can differ from a manual sum divided by total rows. DISTINCT inside an aggregate applies only to that aggregate, not to the whole query. Combining DISTINCT with ORDER BY inside an aggregate is not portable. Finally, aggregate queries without GROUP BY always return exactly one row, even when the table is empty; COUNT returns zero, while SUM and AVG return null. Vendor behavior for empty input and for grouping by expressions may vary slightly.
Best practices
Use COUNT(*) when you want the number of rows and COUNT(column) only when you specifically want non-null values. Alias aggregate results with clear names such as AS total_amount to make output readable. Prefer HAVING for group-level filters and WHERE for row-level filters; applying WHERE first reduces the rows that aggregation must process. Avoid SELECT DISTINCT as a substitute for GROUP BY; they have different semantics. When mixing aggregates and columns, include every non-aggregated column in GROUP BY, or use a functional dependency that the database recognizes. Be explicit about null handling: use COALESCE(SUM(x), 0) when a numeric zero is required instead of null. For large data, filter early and consider indexing the GROUP BY columns. Use DISTINCT inside aggregates only when duplicates must be removed, since it can increase cost. Finally, keep aggregate queries simple and test them against small datasets to verify null and empty-group behavior before relying on them in production.
Frequently asked questions
What is the difference between COUNT(*) and COUNT(column)? COUNT(*) counts all rows in the group, including rows with nulls. COUNT(column) counts only rows where column is not null, so it can return a smaller number. Why does SELECT dept_id, COUNT(*) FROM employees fail? Because dept_id is neither aggregated nor listed in GROUP BY. Standard SQL requires every non-aggregated select column to be functionally dependent on the group keys. Add GROUP BY dept_id to fix it. Can I use an aggregate in a WHERE clause? No. WHERE is evaluated before grouping, so aggregates are not available there. Use HAVING to filter on aggregate results, for example HAVING COUNT(*) > 5. Do aggregate functions ignore nulls? SUM, AVG, MIN, and MAX ignore null inputs and return null if all inputs are null. COUNT(*) counts rows regardless of nulls, while COUNT(expr) ignores nulls.