SQL AGGREGATE

What is SQL AGGREGATE?

SQL aggregate functions perform a calculation on multiple rows and return a single summarized value.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL AGGREGATE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Snowflake, BigQuery, Redshift, SQLite, DuckDB and all ANSI-compliant databases

SQL AGGREGATE Full Explanation

SQL AGGREGATE represents the family of built-in functions (SUM, COUNT, AVG, MIN, MAX, ARRAY_AGG, etc.) that reduce a set of rows to one value. They are most often paired with GROUP BY to return one result per group, but they also work without GROUP BY to aggregate an entire table or subquery. Aggregate functions ignore NULL values unless stated otherwise (e.g., COUNT(*) counts NULLs, while COUNT(column) does not). DISTINCT can be added to aggregate only unique values. Some databases allow custom aggregates through CREATE AGGREGATE. Aggregates cannot normally appear in WHERE clauses; use HAVING for post-aggregation filters. They are deterministic when the underlying data is unchanged.

SQL AGGREGATE Syntax

SELECT AGG_FUNCTION([DISTINCT | ALL] expression)
FROM table_name
[WHERE condition]
[GROUP BY grouping_columns]
[HAVING aggregate_condition];

SQL AGGREGATE Parameters

AGG_FUNCTION (identifier) - Name of the aggregate function such as SUM, COUNT, AVG, MIN, MAX, ARRAY_AGG
expression (any) - Column or expression to aggregate
DISTINCT/ALL (keyword) - OPTIONAL. DISTINCT aggregates unique values; ALL (default) aggregates all non-NULL values

Example Queries Using SQL AGGREGATE

-- Total revenue across all orders
SELECT SUM(total_amount) AS total_revenue
FROM orders;

-- Average order value per customer
SELECT customer_id, AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id;

-- Count of distinct active users last 7 days
SELECT COUNT(DISTINCT user_id) AS dau
FROM sessions
WHERE session_date >= CURRENT_DATE - INTERVAL '7 day';

-- Minimum, maximum, and average salary by department
SELECT department_id,
       MIN(salary)  AS min_salary,
       MAX(salary)  AS max_salary,
       AVG(salary)  AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Expected Output Using SQL AGGREGATE

Each query returns one row per aggregate or per group
For grouped queries, every unique value of GROUP BY columns produces a row containing the aggregated result columns

Use Cases with SQL AGGREGATE

Summarizing numeric data (sales totals, averages, min/max values)
Counting rows, unique users, or events
Building KPI metrics like MRR, DAU, ARPU
Feeding dashboards and reports with pre-aggregated numbers
Data quality checks (e.g., COUNT(*) vs. COUNT(id) to find NULLs)
Creating roll-ups before exporting data or joining with smaller tables

Common Mistakes with SQL AGGREGATE

Using aggregate columns in WHERE instead of HAVING
Forgetting GROUP BY columns, causing SQL errors or unexpected full-table aggregation
Assuming COUNT(column) includes NULLs (it does not)
Omitting DISTINCT when counting unique values
Mixing non-aggregated columns with aggregates without GROUP BY

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is an aggregate function in SQL?

Aggregate functions calculate a single result from a set of rows. Examples include SUM, COUNT, AVG, MIN, and MAX.

When should I use GROUP BY with an aggregate?

Use GROUP BY when you need a separate aggregated value for each subgroup of your data rather than one value for the entire result set.

Does COUNT(*) include NULLs?

Yes. COUNT(*) counts every row. To exclude NULLs, use COUNT(column) which ignores rows where column is NULL.

Can I filter on an aggregate result?

Yes. Use the HAVING clause, not WHERE, to filter based on aggregate values after grouping.