SQL AGGREGATE FUNCTIONS

What are SQL aggregate functions?

Aggregate functions compute a single value from multiple rows, such as totals, averages, counts, minimums, or maximums.

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 FUNCTIONS: Supported in PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, MariaDB, DB2, and most other relational databases.

SQL AGGREGATE FUNCTIONS Full Explanation

SQL aggregate functions summarize data across one or more rows and return a single scalar result. They are most often used with GROUP BY to produce per-group summaries, or without GROUP BY to summarize an entire table. Standard aggregates include COUNT, SUM, AVG, MIN, and MAX. Some dialects add functions like STRING_AGG, MEDIAN, or VAR_SAMP. Aggregates ignore NULL values except COUNT(*), which counts every row. DISTINCT can be applied inside most aggregates to remove duplicates before computation. Aggregate results are calculated after WHERE but before HAVING and ORDER BY, so HAVING is the correct place to filter on aggregated values. Aggregates cannot reference aliases defined in the same SELECT list, and non-aggregated columns in the SELECT clause must appear in GROUP BY, otherwise the query is invalid.

SQL AGGREGATE FUNCTIONS Syntax

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

SQL AGGREGATE FUNCTIONS Parameters

aggregate_function (string) - One of COUNT, SUM, AVG, MIN, MAX, or another supported aggregate.
ALL (DISTINCT) - keyword|||Optional. ALL (default) includes all rows. DISTINCT removes duplicates before aggregation.
expression (any) - Column name or expression to aggregate.
grouping_columns (list) - Columns that define each group when GROUP BY is used.
condition / aggregate_condition (condition) - Boolean expressions for row and group filtering, respectively.

Example Queries Using SQL AGGREGATE FUNCTIONS

-- 1. Count all rows
SELECT COUNT(*) AS row_count
FROM   orders;

-- 2. Total revenue for the whole table
SELECT SUM(total_amount) AS revenue
FROM   orders;

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

-- 4. Customers with more than 5 orders
SELECT   customer_id,
         COUNT(*) AS order_count
FROM     orders
GROUP BY customer_id
HAVING   COUNT(*) > 5;

-- 5. Distinct product styles sold
SELECT COUNT(DISTINCT style_id) AS unique_styles
FROM   order_items;

Expected Output Using SQL AGGREGATE FUNCTIONS

Each query returns a result set containing the calculated scalar values or one row per group as defined by GROUP BY
For example, query 1 returns a single row with the total number of rows in the orders table, while query 3 returns one row per customer with their average order value

Use Cases with SQL AGGREGATE FUNCTIONS

Generating KPIs such as total revenue or average order size
Building dashboards that show counts by category
Identifying top or bottom performers using MAX or MIN
Filtering groups based on aggregated conditions (HAVING)
Preparing data for reporting or further analysis

Common Mistakes with SQL AGGREGATE FUNCTIONS

Selecting non-aggregated columns without including them in GROUP BY
Using WHERE instead of HAVING to filter on aggregated results
Expecting COUNT(column) to include NULLs (it skips them)
Forgetting DISTINCT when counting unique values
Assuming aggregates process after ORDER BY (they process before)

First Introduced In

SQL-86

Frequently Asked Questions

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

COUNT(*) counts every row, including those where the column is NULL. COUNT(column) skips rows with NULL in that column.

How do I filter groups after aggregation?

Use the HAVING clause. WHERE filters individual rows before aggregation, while HAVING filters aggregated groups.

Can I apply DISTINCT inside aggregate functions?

Yes. For example, COUNT(DISTINCT user_id) counts unique users. DISTINCT removes duplicates before the aggregation is calculated.

Do aggregates work with window functions?

Window functions provide per-row results over a window frame, whereas aggregate functions collapse rows into a single value per group. They can be combined in the same query but serve different purposes.