How do aggregate functions handle NULL?

All standard aggregates except COUNT(*) ignore NULL inputs: AVG, SUM, MIN, MAX and COUNT(col) skip NULL rows. COUNT(*) counts every row including those with NULLs. If every input is NULL, AVG/SUM/MIN/MAX return NULL rather than 0.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows in the group regardless of content. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts the number of distinct non-NULL values, which is often what you want for cardinality.

How do I concatenate grouped strings in each database?

MySQL and SQLite use GROUP_CONCAT(expr SEPARATOR ','). PostgreSQL and SQL Server use STRING_AGG(expr, ','). Oracle uses LISTAGG. They differ in separator syntax and ordering clauses, so this is a common portability snag.

Can I combine DISTINCT with an aggregate?

Yes. SUM(DISTINCT col), COUNT(DISTINCT col) and AVG(DISTINCT col) aggregate only over unique values. DISTINCT applies before the aggregate runs. Note that GROUP_CONCAT(DISTINCT ...) is supported in MySQL and SQLite but STRING_AGG handles uniqueness differently.

Does this tool send my query anywhere?

No. The function list is bundled into the page and filtered entirely in your browser. Nothing is uploaded, logged or stored.

What is the SQL Aggregate Functions Reference?

Cross-dialect SQL aggregate function reference covering COUNT, SUM, AVG, MIN, MAX, STRING_AGG, GROUP_CONCAT, ARRAY_AGG and more, with syntax, NULL handling and per-database availability. It runs free in your browser on Gera Tools, with nothing uploaded.

SQL Aggregate Functions Reference

Name: SQL Aggregate Functions Reference
Creator: Gera Tools
License: https://creativecommons.org/licenses/by/4.0/

Get one useful tool a week

Like this tool? Enter your email and we'll send you one genuinely useful Gera tool a week — plus a link to come back to this one. No spam, one-click unsubscribe any time.

What aggregate functions do

An aggregate function collapses many rows into a single value per group — a sum, an average, a row count, a concatenated string. They pair with GROUP BY to produce one result row per group, or run over the whole table with no GROUP BY. This reference lists the common aggregates, their NULL behaviour, and which of MySQL, PostgreSQL, SQLite and SQL Server support each name.

How it works

Each aggregate scans the rows of a group and folds them into one value, ignoring NULL inputs (except COUNT(*)):

SELECT department,
       COUNT(*)              AS headcount,
       AVG(salary)           AS avg_salary,
       SUM(DISTINCT bonus)   AS unique_bonus_total,
       STRING_AGG(name, ', ') AS roster
FROM   employees
GROUP  BY department;

DISTINCT inside an aggregate restricts it to unique values. String aggregation is the least portable area: MySQL/SQLite spell it GROUP_CONCAT while PostgreSQL/SQL Server use STRING_AGG. The tool filters the table as you type and flags which databases provide each function.

String aggregation across dialects

Concatenating grouped strings is the most common portability trap in aggregate SQL. The four major dialects each have their own spelling:

Database	Function	Separator syntax
MySQL	`GROUP_CONCAT(col SEPARATOR ', ')`	Inside the call
SQLite	`GROUP_CONCAT(col, ', ')`	As a second argument
PostgreSQL	`STRING_AGG(col, ', ')`	As a second argument
SQL Server	`STRING_AGG(col, ', ')`	As a second argument
Oracle	`LISTAGG(col, ', ') WITHIN GROUP (ORDER BY col)`	WITHIN GROUP clause

MySQL and SQLite also accept ORDER BY inside GROUP_CONCAT, while PostgreSQL’s STRING_AGG takes an ORDER BY clause in WITHIN GROUP. Getting this right matters whenever you move SQL between databases.

NULL handling — the one exception you must know

Every standard aggregate except COUNT(*) ignores NULL rows silently. This surprises developers when an expected sum or average returns NULL rather than zero: if every value in the group is NULL, the result is NULL. Wrap with COALESCE(SUM(col), 0) whenever a zero result is more appropriate than NULL.

COUNT(*) counts every row, even those where every column is NULL. COUNT(col) counts only the non-NULL values in that column. The two return different numbers whenever NULLs are present.

Tips and notes

COUNT(*) never returns NULL; the all-NULL group still counts its rows.
AVG of an empty or all-NULL set is NULL, not 0 — wrap in COALESCE if needed.
Mixing aggregated and non-aggregated columns requires every plain column in GROUP BY.
FILTER (WHERE ...) (Postgres/SQLite) is a cleaner alternative to SUM(CASE WHEN ...).
SUM(DISTINCT col) sums only unique values — useful for de-duplicating joins before aggregating.