Aggregate Functions

7 Functions

Aggregate functions perform a calculation on a set of values and return a single scalar value. They are often used with the GROUP BY clause to summarize data categories.

Table of Contents

COUNT()SUM()AVG()MIN()MAX()STRING_AGG()COUNT(DISTINCT)
1.

COUNT()

Description

Counts the number of rows that match a specified criterion.

Syntax
COUNT(expression) or COUNT(*)
When to Use

You want to know how many orders have been placed or how many active users exist.

Example Query
-- Count total rows in the orders table
SELECT COUNT(*) FROM orders;
-- Count only orders where status is 'completed'
SELECT COUNT(order_id)
FROM orders
WHERE status = 'completed';
💡 Pro Tip

COUNT(*) counts all rows, including those with NULL values.
COUNT(column_name) counts only non-NULL values in that specific column. Use this distinction wisely!

2.

SUM()

Description

Calculates the total sum of a numeric column.

Syntax
SUM(column_name)
When to Use

Calculating total revenue from sales or total points earned by a player.

Example Query
-- Calculate total revenue from all orders
SELECT SUM(total_amount)
FROM orders;
-- Calculate total sales per category (with GROUP BY)
SELECT category, SUM(sales)
FROM products
GROUP BY category;
💡 Pro Tip

SUM() ignores NULL values. If you try to sum a column where all values are NULL, the result will be NULL, not 0. You can wrap it in COALESCE(SUM(col), 0) to force a zero result.

3.

AVG()

Description

Calculates the average (arithmetic mean) value of a numeric column.

Syntax
AVG(column_name)
When to Use

Finding the average order value (AOV) or the average score of a student class.

Example Query
-- Find the average price of products
SELECT AVG(price)
FROM products;
💡 Pro Tip

Be careful with precision! In some databases, averaging integers might return an integer (rounded down). In PostgreSQL, AVG returns a numeric type for accurate decimals, which is great for financial calculations.

4.

MIN()

Description

Returns the smallest (minimum) value in a set.

Syntax
MIN(column_name)
When to Use

Finding the cheapest product, the earliest order date, or the lowest test score.

Example Query
-- Find the cheapest product price
SELECT MIN(price) FROM products;
-- Find the earliest registration date of a user
SELECT MIN(created_at) FROM users;
💡 Pro Tip

MIN() isn't just for numbers! It works on Dates (earliest date) and Strings (alphabetically first, e.g., "Apple" comes before "Banana").

5.

MAX()

Description

Returns the largest (maximum) value in a set.

Syntax
MAX(column_name)
When to Use

Finding the most expensive item, the latest login time, or the highest XP score.

Example Query
-- Find the most expensive product
SELECT MAX(price) FROM products;
-- Find the most recent activity timestamp
SELECT MAX(last_login) FROM users;
💡 Pro Tip

Like MIN, MAX() works on text too. MAX(name) will return the name that is last in alphabetical order (e.g., "Zebra").

6.

STRING_AGG()

Description

Concatenates values from multiple rows into a single string, separated by a delimiter.

Syntax
STRING_AGG(expression, delimiter)
When to Use

You want a comma-separated list of all email addresses belonging to a specific user group.

Example Query
-- List all project names for each employee in one line
SELECT employee_id, STRING_AGG(project_name, ', ')
FROM employee_projects
GROUP BY employee_id;
💡 Pro Tip

This is incredibly useful for reporting. Instead of getting 5 rows for one user, you get 1 row with all their items listed clearly. You can also order the list inside the function: STRING_AGG(name, ', ' ORDER BY name).

7.

COUNT(DISTINCT)

Description

Counts the number of unique non-null values.

Syntax
COUNT(DISTINCT column_name)
When to Use

You want to know how many unique customers made a purchase today (ignoring if the same customer bought 5 times).

Example Query
-- Count how many unique customers placed an order
SELECT COUNT(DISTINCT customer_id)
FROM orders;
💡 Pro Tip

This is computationally more expensive than a regular COUNT, so use it only when you genuinely need to eliminate duplicates from your count.