SQL Aggregate Functions: Stop Guessing, Start Calculating

If you've ever needed to answer questions like "How many orders did we get this month?", "What's our total revenue?", or "Who's our highest-paid employee?" — you need SQL aggregate functions. They're the workhorses of data analysis, and once you understand them, you'll wonder how you ever got by without them.

In this guide, we'll cover the five essential SQL aggregate functions — COUNT, SUM, AVG, MIN, and MAX — with realistic examples you can actually use in your projects.

What Are Aggregate Functions?

Aggregate functions take a group of rows and return a single value. Instead of seeing one result per row, you get a summary. Think of them as the SQL equivalent of a calculator that works across your entire dataset.

We'll use a sample orders table throughout this article:

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    product_name VARCHAR(100),
    quantity     INT,
    unit_price   DECIMAL(10, 2),
    order_date   DATE,
    status       VARCHAR(20)
);

And some sample data to work with:

INSERT INTO orders (order_id, customer_id, product_name, quantity, unit_price, order_date, status) VALUES
(1,  101, 'Wireless Headphones', 2,  79.99,  '2024-03-01', 'completed'),
(2,  102, 'USB-C Hub',           1,  49.99,  '2024-03-02', 'completed'),
(3,  101, 'Mechanical Keyboard', 1,  129.99, '2024-03-05', 'completed'),
(4,  103, 'Webcam HD',           3,  59.99,  '2024-03-07', 'pending'),
(5,  104, 'Monitor Stand',       1,  39.99,  '2024-03-10', 'completed'),
(6,  102, 'Wireless Mouse',      2,  34.99,  '2024-03-12', 'cancelled'),
(7,  105, 'Laptop Sleeve',       4,  24.99,  '2024-03-15', 'completed'),
(8,  103, 'HDMI Cable',          2,  14.99,  '2024-03-18', 'completed'),
(9,  106, 'Desk Lamp',           1,  44.99,  '2024-03-20', 'pending'),
(10, 101, 'Cable Organizer',     5,   9.99,  '2024-03-22', 'completed');

---

1. COUNT — How Many Rows Are There?

COUNT answers "how many?" It's arguably the most-used aggregate function in SQL.

Count all rows

SELECT COUNT(*) AS total_orders
FROM orders;

Result:
| total_orders |
|---|
| 10 |

Count non-NULL values in a column

COUNT(column_name) skips NULL values, while COUNT(*) counts every row.

SELECT COUNT(order_id) AS orders_with_id
FROM orders;

Count with a filter

How many orders were completed?

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed';

Result:
| completed_orders |
|---|
| 7 |

Count distinct values

How many unique customers placed orders?

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result:
| unique_customers |
|---|
| 6 |

---

2. SUM — Add It All Up

SUM totals up numeric values. Perfect for revenue calculations, quantity totals, and similar tasks.

Total quantity sold

SELECT SUM(quantity) AS total_items_sold
FROM orders;

Result:
| total_items_sold |
|---|
| 22 |

Total revenue from completed orders

Here we calculate revenue by multiplying quantity × unit_price for each row, then summing it all:

SELECT SUM(quantity * unit_price) AS total_revenue
FROM orders
WHERE status = 'completed';

Result:
| total_revenue |
|---|
| 704.81 |

Revenue per customer

This is where SUM gets really powerful when combined with GROUP BY:

SELECT 
    customer_id,
    SUM(quantity * unit_price) AS customer_revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY customer_revenue DESC;

Result:
| customer_id | customer_revenue |
|---|---|
| 101 | 369.95 |
| 105 | 99.96 |
| 103 | 29.98 |
| 104 | 39.99 |
| 102 | 49.99 |

Customer 101 is clearly your best customer!

---

3. AVG — Find the Average

AVG calculates the mean of a set of values. Great for understanding typical behavior.

Average order value

SELECT AVG(unit_price) AS avg_product_price
FROM orders;

Result:
| avg_product_price |
|---|
| 48.99 |

Average items per order

SELECT AVG(quantity) AS avg_items_per_order
FROM orders;

Result:
| avg_items_per_order |
|---|
| 2.2000 |

Average revenue per completed order

SELECT ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
FROM orders
WHERE status = 'completed';

Result:
| avg_order_value |
|---|
| 100.69 |

Pro tip: AVG ignores NULL values automatically. If a column has NULLs, they won't drag down your average — but be aware of this behavior to avoid surprises.

---

4. MIN — Find the Smallest Value

MIN returns the lowest value in a column. Works with numbers, dates, and even strings.

Cheapest product

SELECT MIN(unit_price) AS cheapest_product_price
FROM orders;

Result:
| cheapest_product_price |
|---|
| 9.99 |

Earliest order date

SELECT MIN(order_date) AS first_order_date
FROM orders;

Result:
| first_order_date |
|---|
| 2024-03-01 |

First order per customer

SELECT 
    customer_id,
    MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id;

Result:
| customer_id | first_order_date |
|---|---|
| 101 | 2024-03-01 |
| 102 | 2024-03-02 |
| 103 | 2024-03-07 |
| 104 | 2024-03-10 |
| 105 | 2024-03-15 |
| 106 | 2024-03-20 |

---

5. MAX — Find the Largest Value

MAX is the mirror image of MIN — it returns the highest value.

Most expensive product

SELECT MAX(unit_price) AS most_expensive_product
FROM orders;

Result:
| most_expensive_product |
|---|
| 129.99 |

Most recent order date

SELECT MAX(order_date) AS last_order_date
FROM orders;

Result:
| last_order_date |
|---|
| 2024-03-22 |

Biggest single order value per customer

SELECT 
    customer_id,
    MAX(quantity * unit_price) AS biggest_order_value
FROM orders
GROUP BY customer_id
ORDER BY biggest_order_value DESC;

---

Combining Multiple Aggregates

The real magic happens when you use several aggregate functions together in one query. Here's a full sales summary report:

SELECT 
    COUNT(*)                              AS total_orders,
    COUNT(DISTINCT customer_id)           AS unique_customers,
    SUM(quantity)                         AS total_items_sold,
    ROUND(SUM(quantity * unit_price), 2)  AS gross_revenue,
    ROUND(AVG(quantity * unit_price), 2)  AS avg_order_value,
    MIN(unit_price)                       AS cheapest_item,
    MAX(unit_price)                       AS priciest_item
FROM orders
WHERE status = 'completed';

Result:
| total_orders | unique_customers | total_items_sold | gross_revenue | avg_order_value | cheapest_item | priciest_item |
|---|---|---|---|---|---|---|
| 7 | 5 | 15 | 704.81 | 100.69 | 9.99 | 129.99 |

One query, a full business snapshot. That's the power of aggregate functions.

---

Common Mistakes to Avoid

1. Forgetting to use GROUP BY when selecting non-aggregated columns

This will throw an error in most databases:

-- ❌ Wrong — product_name isn't aggregated or grouped
SELECT product_name, SUM(quantity)
FROM orders;

-- ✅ Correct
SELECT product_name, SUM(quantity) AS total_sold
FROM orders
GROUP BY product_name;

2. Using WHERE instead of HAVING to filter aggregated results

WHERE filters rows before aggregation. HAVING filters after. If you want to filter based on an aggregate result, you need HAVING:

-- ❌ Wrong — can't use SUM() in WHERE
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
WHERE SUM(quantity * unit_price) > 100
GROUP BY customer_id;

-- ✅ Correct
SELECT customer_id, SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY customer_id
HAVING SUM(quantity * unit_price) > 100;

3. Confusing COUNT(*) and COUNT(column)

COUNT(*) counts all rows including NULLs. COUNT(column) skips NULLs in that column. Choose wisely depending on what you actually want to count.

4. Not accounting for NULL in AVG

If some rows have NULL in the column you're averaging, those rows are excluded from both the sum and the count. This can make your average higher than expected.

-- Example: if 3 out of 10 rows have NULL discount, AVG only divides by 7
SELECT AVG(discount_amount) FROM orders;

---

Key Takeaways

• COUNT — Count rows or distinct values. Use COUNT(*) for all rows, COUNT(col) to skip NULLs.
• SUM — Add up numeric values. Combine with GROUP BY for per-group totals.
• AVG — Calculate the mean. Remember it ignores NULLs.
• MIN / MAX — Find the smallest or largest value. Works on numbers, dates, and strings.
• Combine multiple aggregates in a single query for powerful summary reports.
• Use HAVING (not WHERE) to filter results based on aggregate values.

These five functions are the foundation of nearly every analytical SQL query you'll ever write. Master them, and you'll be answering data questions that used to take spreadsheets and manual work — all with a few clean lines of SQL.

---

Have a question about aggregate functions, or a favorite use case I didn't cover? Drop it in the comments — I'd love to hear how you're using these in your own projects!