SQL Aggregate Functions: Your Complete Guide
Learn the 7 essential aggregation techniques that transform raw data into meaningful insights. From counting rows to calculating totals, these functions are fundamental to SQL analytics.
Why Aggregate Functions Are Essential for Data Analysis
Raw data in databases is detailed - every order, every product, every transaction. Business questions require summaries: How many customers bought last month? What's our total revenue? Which category sells best? Aggregate functions answer these questions by condensing thousands of rows into meaningful insights.
Every analytics dashboard, business report, and KPI metric relies on aggregation. Sales reports need SUM for totals and AVG for averages. Performance metrics use COUNT to track volumes and MAX/MIN to identify outliers. Without aggregate functions, you'd be stuck examining individual rows instead of seeing patterns and trends.
The power multiplies when you combine aggregate functions with GROUP BY. Instead of one total across all data, you get subtotals by category, region, time period, or any dimension. This transforms simple sums into sophisticated analytics that drive business decisions.
This guide covers the seven core aggregation techniques you'll use constantly. Understanding when to use COUNT vs SUM, WHERE vs HAVING, and how to combine multiple aggregates makes you effective at turning data into insights.
Common Mistakes with Aggregate Functions
Using Aggregates in WHERE Instead of HAVING
Trying to filter on COUNT(*) or SUM() in a WHERE clause causes errors. WHERE filters individual rows before grouping, so aggregate functions don't exist yet. Use HAVING to filter based on aggregate results: HAVING COUNT(*) > 5, not WHERE COUNT(*) > 5.
Forgetting Columns in GROUP BY
Every non-aggregated column in SELECT must appear in GROUP BY. If you SELECT category, region, COUNT(*) but only GROUP BY category, most databases throw an error. The database doesn't know which region value to show for each category group.
Misunderstanding NULL Behavior
Aggregate functions ignore NULL values (except COUNT(*)). This catches beginners off guard when AVG(column) calculates differently than expected. If half your rows are NULL, AVG divides by the count of non-NULL values, not total rows. Use COUNT(column) vs COUNT(*) to see the difference.
Expecting SUM to Return Zero Instead of NULL
When SUM operates on a group with no rows or only NULL values, it returns NULL, not zero. This surprises developers who expect numeric results. Use COALESCE to handle this: COALESCE(SUM(amount), 0) converts NULL results to zero.
Mixing Aggregated and Non-Aggregated Columns Incorrectly
You can't mix aggregated (COUNT, SUM) and non-aggregated columns in SELECT without GROUP BY. The query SELECT category, COUNT(*) FROM products fails because SQL doesn't know which category to show with the count. Add GROUP BY category to fix this.
7 Essential Aggregation Techniques
These aggregation functions and techniques form the foundation of SQL data analysis and reporting.
COUNT - Counting Rows and Values
Count total rows, non-NULL values, or distinct values
Examples:
-- Count all rows
SELECT COUNT(*) as total_products FROM products;
-- Count non-NULL values in a column
SELECT COUNT(discount_price) as discounted_products FROM products;
-- Count unique values
SELECT COUNT(DISTINCT category) as unique_categories FROM products;
-- Count with conditions
SELECT COUNT(*) FILTER (WHERE price > 100) as expensive_products
FROM products;When to Use:
COUNT is the most frequently used aggregate function. Use COUNT(*) to count all rows including NULL values. Use COUNT(column) to count only non-NULL values. COUNT(DISTINCT column) finds unique values. Essential for analytics and reporting.
SUM - Calculate Totals
Add up numeric values across rows
Examples:
-- Total revenue
SELECT SUM(total_amount) as total_revenue
FROM orders
WHERE order_status = 'completed';
-- Sum by category with GROUP BY
SELECT category, SUM(stock_quantity) as total_stock
FROM products
GROUP BY category
ORDER BY total_stock DESC;When to Use:
SUM adds numeric values together. Use it for calculating totals like revenue, inventory quantities, or hours worked. SUM ignores NULL values. Combine with GROUP BY to calculate subtotals for different categories or time periods.
AVG - Calculate Averages
Find the mean value of numeric columns
Examples:
-- Average order value
SELECT AVG(total_amount) as avg_order_value
FROM orders;
-- Average by customer segment
SELECT customer_segment, AVG(total_amount) as avg_order
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY customer_segment;
-- Round averages for readability
SELECT category, ROUND(AVG(price), 2) as avg_price
FROM products
GROUP BY category;When to Use:
AVG calculates the arithmetic mean. Use it for metrics like average order value, average rating, or average response time. AVG ignores NULL values, so the denominator only includes non-NULL rows. Always consider rounding for money values.
MAX - Find Maximum Values
Get the highest value in a column
Examples:
-- Most expensive product
SELECT MAX(price) as highest_price FROM products;
-- Latest order date per customer
SELECT customer_id, MAX(order_date) as last_order_date
FROM orders
GROUP BY customer_id;
-- Find the actual row with max value
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);When to Use:
MAX returns the largest value in a column. Use it to find highest prices, latest dates, or maximum quantities. When you need the entire row with the max value (not just the max), use a subquery as shown in the third example.
MIN - Find Minimum Values
Get the lowest value in a column
Examples:
-- Cheapest product
SELECT MIN(price) as lowest_price FROM products;
-- Earliest order date by status
SELECT order_status, MIN(order_date) as first_order
FROM orders
GROUP BY order_status;
-- Products at minimum price
SELECT product_name, price
FROM products
WHERE price = (SELECT MIN(price) FROM products);When to Use:
MIN returns the smallest value. Use it to find lowest prices, earliest dates, or minimum quantities. Like MAX, when you need the complete row rather than just the minimum value, combine MIN with a subquery in the WHERE clause.
GROUP BY - Organize Data into Groups
Create summary rows for each group of values
Examples:
-- Sales by category
SELECT
category,
COUNT(*) as product_count,
AVG(price) as avg_price,
SUM(stock_quantity) as total_stock
FROM products
GROUP BY category
ORDER BY product_count DESC;
-- Multiple grouping columns
SELECT
EXTRACT(YEAR FROM order_date) as year,
EXTRACT(MONTH FROM order_date) as month,
COUNT(*) as order_count,
SUM(total_amount) as revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year DESC, month DESC;When to Use:
GROUP BY is essential for creating summaries. Every non-aggregated column in SELECT must appear in GROUP BY. Use it to break down totals by category, time period, region, or any other dimension. Enables powerful analytics and reporting.
HAVING - Filter Aggregated Results
Apply conditions to grouped data after aggregation
Examples:
-- Customers with more than 5 orders
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;
-- Categories with average price above threshold
SELECT category, AVG(price) as avg_price, COUNT(*) as count
FROM products
GROUP BY category
HAVING AVG(price) > 50 AND COUNT(*) >= 10
ORDER BY avg_price DESC;When to Use:
HAVING filters groups after aggregation, while WHERE filters rows before grouping. Use HAVING when you need conditions based on COUNT, SUM, AVG, or other aggregates. You can combine WHERE and HAVING in the same query - WHERE filters input rows, HAVING filters result groups.
Your First Steps with Aggregate Functions
Step 1: Practice Basic Aggregates Without Grouping
Start by using aggregate functions on entire tables to get single summary values. This helps you understand what each function does before adding complexity with GROUP BY.
-- Simple aggregates on whole table
SELECT COUNT(*) as total_orders FROM orders;
SELECT SUM(total_amount) as total_revenue FROM orders;
SELECT AVG(total_amount) as avg_order_value FROM orders;
SELECT MIN(order_date) as first_order, MAX(order_date) as last_order FROM orders;Step 2: Add GROUP BY for Subtotals
Group your data by one column to see breakdowns. Start with one grouping column and one aggregate, then gradually add more of each.
-- Group by one column
SELECT category, COUNT(*) as count
FROM products
GROUP BY category;
-- Add multiple aggregates
SELECT category, COUNT(*) as count, AVG(price) as avg_price, SUM(stock_quantity) as total_stock
FROM products
GROUP BY category
ORDER BY count DESC;Step 3: Filter Groups with HAVING
Once you're comfortable with GROUP BY, add HAVING to filter your results based on aggregate values. Combine WHERE (filters input) and HAVING (filters output) for powerful queries.
-- Use HAVING to filter aggregated results
SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_spent
FROM orders
WHERE order_date >= '2025-01-01' -- Filter input rows
GROUP BY customer_id
HAVING COUNT(*) >= 5 -- Filter result groups
ORDER BY total_spent DESC;Frequently Asked Questions About Aggregate Functions
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts all rows in a group, including rows with NULL values. COUNT(column) only counts rows where that specific column is not NULL. If you need the total row count, use COUNT(*). If you want to count how many rows have a value in a specific column, use COUNT(column).
Why can't I use aggregate functions in WHERE clause?
WHERE filters individual rows before grouping happens, so aggregate functions don't make sense there yet. If you need to filter based on aggregate results, use HAVING instead. For example, use HAVING COUNT(*) > 5, not WHERE COUNT(*) > 5.
How do aggregate functions handle NULL values?
All aggregate functions (COUNT, SUM, AVG, MIN, MAX) ignore NULL values except COUNT(*). This means AVG(column) divides by the count of non-NULL values, not all rows. If a group has only NULL values, SUM returns NULL (not zero), and COUNT returns 0.
Can I use multiple aggregate functions in one query?
Yes, you can combine as many aggregate functions as needed in a single SELECT: SELECT COUNT(*), AVG(price), MIN(price), MAX(price), SUM(quantity) FROM products. Each aggregate operates independently on the same set of rows or groups.
What happens if I forget to include a column in GROUP BY?
If you select a non-aggregated column without including it in GROUP BY, most databases will throw an error. PostgreSQL and MySQL (with strict mode) require every non-aggregated column in SELECT to appear in GROUP BY. This prevents ambiguous results where SQL doesn't know which row's value to show.
How do I calculate percentage of total with aggregates?
Use a subquery or window function. With a subquery: SELECT category, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products) as percentage FROM products GROUP BY category. The window function approach (covered in advanced topics) is often cleaner for this use case.
Can I use GROUP BY without aggregate functions?
Yes, but it's equivalent to DISTINCT. SELECT category FROM products GROUP BY category gives the same result as SELECT DISTINCT category FROM products. However, GROUP BY is more powerful when you need to combine it with aggregate functions for summaries.
How do I find the row with the maximum or minimum value?
Use a subquery in WHERE clause. To find the most expensive product: SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products). This is better than sorting and limiting because it handles ties correctly - if multiple products have the same maximum price, you'll get all of them.
Transform Data into Insights
This guide covers essential aggregate functions. The complete book provides comprehensive SQL training from fundamentals to expert techniques, with hundreds of real-world examples.
What You'll Get:
- ✓350+ practical SQL examples covering beginner to expert topics
- ✓140+ SQL interview questions with detailed solutions
- ✓Complete e-commerce database for hands-on practice
- ✓SQL code library with 380+ ready-to-use statements
- ✓2 bonus SQL cheat sheets: Glossary and Dialect Differences
Trusted by developers worldwide
4.8/5 rating - Bestselling SQL book