Aggregate Functions in SQL: COUNT, SUM, AVG, MIN, MAX

---

Table of Contents

1. Introduction
2. What are Aggregate Functions?
3. Syntax of Aggregate Functions
4. Using COUNT() to Count Records
5. Counting Non-NULL vs All Rows
6. Using SUM() for Totaling Values
7. Applying SUM() with Filters
8. Using AVG() to Find Averages
9. Ignoring NULL in AVG()
10. Using MIN() and MAX() to Find Extremes
11. Aggregate Functions with GROUP BY
12. Using HAVING with Aggregate Results
13. Combining Aggregates in One Query
14. Nesting Aggregate Functions
15. Aggregate Functions in Joins
16. Handling NULL Values in Aggregations
17. Performance Considerations
18. Common Mistakes and How to Avoid Them
19. Best Practices for Aggregation
20. Summary and What’s Next

---

1. Introduction

SQL aggregate functions are essential for data analysis. They allow you to perform calculations across rows — like counting, totaling, averaging, and finding the maximum or minimum. Whether you’re generating reports, dashboards, or analytics, these functions are foundational.

---

2. What are Aggregate Functions?

Aggregate functions take a set of values (like a column) and return a single summarized result.

Most common:

• COUNT(): total number of rows or values
• SUM(): total of numeric values
• AVG(): average of numeric values
• MIN(): lowest value
• MAX(): highest value

---

3. Syntax of Aggregate Functions

sqlCopyEditSELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
[WHERE condition];

Example:

sqlCopyEditSELECT COUNT(*) FROM users;
SELECT AVG(price) FROM products;

---

4. Using COUNT() to Count Records

COUNT() returns the number of rows that match a condition.

sqlCopyEditSELECT COUNT(*) FROM employees;
SELECT COUNT(name) FROM employees;

• COUNT(*) counts all rows
• COUNT(column) counts only non-NULL values in that column

---

5. Counting Non-NULL vs All Rows

sqlCopyEdit-- Count all employees
SELECT COUNT(*) FROM employees;

-- Count employees with recorded emails
SELECT COUNT(email) FROM employees;

If email has NULL values, those rows will be excluded in the second query.

---

6. Using SUM() for Totaling Values

SUM() adds up all values in a numeric column.

sqlCopyEditSELECT SUM(salary) FROM employees;

Returns the total salary expense.

---

7. Applying SUM() with Filters

sqlCopyEditSELECT SUM(amount) FROM orders
WHERE status = 'completed';

This returns the total value of all completed orders.

---

8. Using AVG() to Find Averages

AVG() returns the arithmetic mean:

sqlCopyEditSELECT AVG(price) FROM products;

Useful for finding average prices, ages, ratings, etc.

---

9. Ignoring NULL in AVG()

AVG() ignores NULL values:

sqlCopyEditSELECT AVG(rating) FROM reviews;

Only includes rows where rating is not NULL.

---

10. Using MIN() and MAX() to Find Extremes

sqlCopyEditSELECT MIN(price) AS lowest_price, MAX(price) AS highest_price 
FROM products;

This gives both minimum and maximum in a single query.

---

11. Aggregate Functions with GROUP BY

GROUP BY combines rows with the same value into groups, allowing you to apply aggregate functions to each group.

sqlCopyEditSELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

---

12. Using HAVING with Aggregate Results

HAVING filters after aggregation (unlike WHERE).

sqlCopyEditSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 60000;

---

13. Combining Aggregates in One Query

sqlCopyEditSELECT 
  COUNT(*) AS total_orders,
  SUM(amount) AS total_sales,
  AVG(amount) AS avg_order_value
FROM orders;

Returns multiple summaries in one result.

---

14. Nesting Aggregate Functions

You can use aggregates in subqueries:

sqlCopyEditSELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

This returns employees earning more than the average.

---

15. Aggregate Functions in Joins

sqlCopyEditSELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

Get each user’s order count using a join + group.

---

16. Handling NULL Values in Aggregations

• COUNT(column): ignores NULL
• SUM(), AVG(): skip NULL
• MIN()/MAX() typically ignore NULL

If a column is entirely NULL, the aggregate may return NULL.

---

17. Performance Considerations

• Aggregations on indexed columns are faster
• Avoid unnecessary columns in SELECT
• Use filters (WHERE, HAVING) to reduce row count before aggregating
• Prefer COUNT(*) for full row counts over COUNT(column) unless filtering nulls

---

18. Common Mistakes and How to Avoid Them

Mistake	Correction
Using WHERE instead of HAVING	Use HAVING to filter aggregated results
Forgetting to GROUP BY	Always GROUP BY non-aggregated columns
Assuming NULL = 0	Use COALESCE() or expect NULL to be skipped
Comparing aggregates in WHERE	Move those comparisons to HAVING

---

19. Best Practices for Aggregation

• Name your output with aliases (AS)
• Use GROUP BY for per-category summaries
• Use HAVING for aggregate-based filtering
• Handle NULL explicitly using COALESCE() if needed
• Keep queries lean — only select what you need

---

20. Summary and What’s Next

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX are at the heart of data analytics in SQL. They allow you to turn raw data into meaningful insights.