KAHIBARO
Discord Login Register ☰
☰

10.10 Aggregate Functions

Table of Contents

Understanding Aggregate Functions

Aggregate functions help you calculate a single value from many rows. Instead of working on one row at a time, they look at a group of rows and return one result, for example a total, an average, or a maximum.

You will use aggregate functions constantly in reporting, dashboards, and analytics.

Typical aggregates include:

FunctionDescriptionCommon use example
COUNTNumber of rows or non-null valuesHow many users signed up
SUMSum of numeric valuesTotal revenue
AVGAverage of numeric valuesAverage order value
MINSmallest valueFirst signup date
MAXLargest valueBiggest purchase amount

All of these return one value for a set of rows.

Important rule:
Aggregate functions compute one result per group of rows.
If you use aggregate functions together with regular columns, you must use a GROUP BY clause or you will get an error in most SQL databases.

In the examples, imagine a table:

sql 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER,
    amount      NUMERIC(10, 2),
    status      VARCHAR(20),         -- 'pending', 'paid', 'canceled'
    created_at  TIMESTAMP
);

COUNT

COUNT returns how many rows match a condition.

Variants:

ExpressionWhat it counts
COUNT(*)All rows, including rows with NULL in some columns
COUNT(column)Rows where column is not NULL
COUNT(DISTINCT column)Unique, non-null values in column

Basic COUNT examples

Count all orders:

sql 
SELECT COUNT(*) AS total_orders
FROM orders;

Count all paid orders:

sql 
SELECT COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid';

Count users that have at least one order (ignores NULL user_id):

sql 
SELECT COUNT(user_id) AS users_with_orders
FROM orders;

If some rows have user_id = NULL, COUNT(user_id) will not include them.

Count distinct users who have made at least one order:

sql 
SELECT COUNT(DISTINCT user_id) AS unique_customers
FROM orders;

COUNT with GROUP BY

Count orders per status:

sql 
SELECT status, COUNT(*) AS count_orders
FROM orders
GROUP BY status;

Possible result:

statuscount_orders
pending12
paid35
canceled3

Each group of rows with the same status produces one count.

SUM

SUM adds up numeric values.

Total revenue from paid orders:

sql 
SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'paid';

If there are no matching rows, most databases return NULL for SUM(amount), not 0. You often want to handle this:

sql 
SELECT COALESCE(SUM(amount), 0) AS total_revenue
FROM orders
WHERE status = 'paid';

SUM with GROUP BY

Revenue per user:

sql 
SELECT user_id, SUM(amount) AS user_revenue
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

Revenue per status:

sql 
SELECT status, SUM(amount) AS revenue
FROM orders
GROUP BY status;

SUM with expressions

You can sum expressions, not only columns.

Sum of a discounted amount, for example 10 percent off:

sql 
SELECT SUM(amount * 0.9) AS discounted_revenue
FROM orders
WHERE status = 'paid';

Sum of orders per day:

sql 
SELECT
    DATE(created_at) AS order_date,
    SUM(amount)      AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY DATE(created_at)
ORDER BY order_date;

AVG

AVG calculates the average of numeric values.

Average order amount:

sql 
SELECT AVG(amount) AS avg_order_amount
FROM orders
WHERE status = 'paid';

Average order amount per user:

sql 
SELECT user_id, AVG(amount) AS avg_order_amount
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

Average daily revenue:

sql 
SELECT
    DATE(created_at) AS day,
    AVG(amount)      AS avg_order_amount
FROM orders
WHERE status = 'paid'
GROUP BY DATE(created_at)
ORDER BY day;

Like SUM, if there are no matching rows, AVG returns NULL.

Rule: AVG(column) ignores NULL values.
Only non-null values are included in the average.

Example:

MIN and MAX

MIN returns the smallest value and MAX returns the largest.

First and last order dates:

sql 
SELECT
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order
FROM orders;

Smallest and largest order amounts:

sql 
SELECT
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders
WHERE status = 'paid';

Per user:

sql 
SELECT
    user_id,
    MIN(amount) AS min_order_amount,
    MAX(amount) AS max_order_amount
FROM orders
GROUP BY user_id;

MIN and MAX also ignore NULL values.

MIN and MAX with non-numeric columns

You can use them on dates and text.

Earliest and latest signup date:

sql 
SELECT
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order
FROM orders;

Alphabetically first and last status:

sql 
SELECT
    MIN(status) AS first_status,
    MAX(status) AS last_status
FROM orders;

This is not often useful for text, but it works.

Using Aggregate Functions with GROUP BY

Aggregate functions are often combined with GROUP BY. Every group produces one row.

General pattern:

sql 
SELECT
    group_column_1,
    group_column_2,
    AGG_FUNC(column)  AS some_value
FROM table
GROUP BY group_column_1, group_column_2;

Rule: Every column in SELECT that is not inside an aggregate function must appear in GROUP BY.

For example, revenue per day and status:

sql 
SELECT
    DATE(created_at)   AS day,
    status,
    SUM(amount)        AS total_amount,
    COUNT(*)           AS order_count
FROM orders
GROUP BY DATE(created_at), status
ORDER BY day, status;

Here:

If you tried to select amount directly, like:

sql 
SELECT DATE(created_at), amount, SUM(amount) FROM orders GROUP BY DATE(created_at);

Most databases would give an error, because amount is neither grouped nor aggregated.

Aggregate Functions with WHERE and HAVING

You can filter rows before aggregation with WHERE, and filter groups after aggregation with HAVING.

WHERE filters rows before aggregation

For example, only paid orders:

sql 
SELECT user_id, SUM(amount) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

The WHERE clause removes non-paid orders from the input.

HAVING filters groups after aggregation

For example, show only users whose total revenue is over 100:

sql 
SELECT user_id, SUM(amount) AS total_revenue
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING SUM(amount) > 100;

Here, HAVING uses SUM(amount), which is an aggregate result.

Filter days where there were at least 10 paid orders:

sql 
SELECT
    DATE(created_at) AS day,
    COUNT(*)         AS orders_count
FROM orders
WHERE status = 'paid'
GROUP BY DATE(created_at)
HAVING COUNT(*) >= 10
ORDER BY day;

Rule:

  • Use WHERE to filter individual rows before aggregates.
  • Use HAVING to filter whole groups after aggregates.

Combining Multiple Aggregates

You can use several aggregate functions in the same query.

Overall statistics:

sql 
SELECT
    COUNT(*)       AS total_orders,
    SUM(amount)    AS total_amount,
    AVG(amount)    AS avg_amount,
    MIN(amount)    AS min_amount,
    MAX(amount)    AS max_amount
FROM orders
WHERE status = 'paid';

Per user:

sql 
SELECT
    user_id,
    COUNT(*)       AS orders_count,
    SUM(amount)    AS total_spent,
    AVG(amount)    AS avg_order,
    MIN(amount)    AS smallest_order,
    MAX(amount)    AS largest_order
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

DISTINCT with Aggregate Functions

You can combine DISTINCT with some aggregates.

Count distinct users per status:

sql 
SELECT
    status,
    COUNT(DISTINCT user_id) AS unique_users
FROM orders
GROUP BY status;

Average of distinct amounts:

sql 
SELECT AVG(DISTINCT amount) AS avg_unique_amount
FROM orders
WHERE status = 'paid';

This ignores duplicate amounts before averaging.

Be careful: DISTINCT inside aggregates can be slower on large data, since the database must remove duplicates first.

Aggregate Functions and NULL Values

How aggregates treat NULL:

FunctionHandles NULL as
COUNT(*)Counts all rows, including rows with NULL columns
COUNT(column)Ignores rows where column is NULL
SUM(column)Ignores NULL values
AVG(column)Ignores NULL values
MIN(column)Ignores NULL values
MAX(column)Ignores NULL values

Example table numbers:

value
10
NULL
20

Then:

If a group has no non-null values, aggregates like SUM, AVG, MIN, MAX return NULL.

You can wrap them with COALESCE to replace NULL with a default:

sql 
SELECT
    COALESCE(SUM(amount), 0) AS total_amount
FROM orders
WHERE status = 'non_existing_status';

Practical Examples

Here are some realistic queries that use aggregate functions.

Daily revenue and order count

sql 
SELECT
    DATE(created_at) AS day,
    COUNT(*)         AS orders_count,
    SUM(amount)      AS total_revenue,
    AVG(amount)      AS avg_order_amount
FROM orders
WHERE status = 'paid'
GROUP BY DATE(created_at)
ORDER BY day;

Top 5 customers by total revenue

sql 
SELECT
    user_id,
    COUNT(*)    AS orders_count,
    SUM(amount) AS total_revenue
FROM orders
WHERE status = 'paid'
GROUP BY user_id
ORDER BY total_revenue DESC
LIMIT 5;

Number of orders and average amount per status

sql 
SELECT
    status,
    COUNT(*)    AS orders_count,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY status;

Users with at least 3 orders

sql 
SELECT
    user_id,
    COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 3;

Percentage of revenue by status

Use aggregate functions in expressions:

sql 
SELECT
    status,
    SUM(amount)                                             AS revenue,
    ROUND(
        SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (),
        2
    )                                                       AS revenue_percent
FROM orders
GROUP BY status;

Explanation, without going deep into window functions:

Summary

Aggregate functions are central to reporting and analytics in SQL:

You will combine these with GROUP BY, WHERE, HAVING, and joins to answer almost any statistical question about your data.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3133 / 24h
Impressum / Imprint
Datenschutz / Data Policy