Aggregate Functions: COUNT, SUM, AVG, MIN, MAX0%

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

Beginner12 min read•Updated: 2026-09-12

Aggregate Functions: COUNT, SUM, AVG, MIN, and MAX

Up to this point, our queries have operated on individual rows (scalar operations). However, business intelligence and executive reporting require summarizing data across thousands or millions of rows: calculating total quarterly sales, finding the maximum salary, or determining average product ratings. These calculations are performed using Aggregate Functions.

1. The Big 5 Aggregate Functions

Function	Purpose	Input Types	Treats NULL as...
`COUNT()`	Counts the number of rows or non-null values.	Any	`COUNT(*)` counts all rows; `COUNT(col)` ignores NULL.
`SUM()`	Calculates the total sum of values.	Numeric	Ignored (Skipped).
`AVG()`	Calculates the arithmetic mean.	Numeric	Ignored (Skipped).
`MIN()`	Finds the minimum value.	Numeric, Date, String	Ignored (Skipped).
`MAX()`	Finds the maximum value.	Numeric, Date, String	Ignored (Skipped).

2. Practical Aggregation Examples

1SELECT 
  COUNT(*) AS total_orders,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS average_order_value,
  MIN(total_amount) AS smallest_order,
  MAX(total_amount) AS largest_order,
  MIN(order_date) AS earliest_order,
  MAX(order_date) AS most_recent_order
9FROM orders;

3. The `COUNT(*)` vs `COUNT(column)` Distinction

One of the most critical concepts in SQL is the difference between COUNT(*) and COUNT(col):

 Table: employees (5 rows total)
 +----+----------+------------+
 | id | name     | commission |
 +----+----------+------------+
 |  1 | Aarav    | 500.00     |
 |  2 | Priya    | NULL       |
 |  3 | Rohan    | 1200.00    |
 |  4 | Diya     | NULL       |
 |  5 | Kabir    | 800.00     |
 +----+----------+------------+
11 
 SELECT COUNT(*) FROM employees;           --> Returns 5 (Counts total rows)
 SELECT COUNT(commission) FROM employees;  --> Returns 3 (Counts ONLY non-null values!)

4. How `AVG()` Handles NULL Values

Because aggregate functions skip NULL rows, AVG() computes the mean divided only by the count of non-null rows:

1-- In the table above, commission has values: 500, 1200, 800 (and 2 NULLs).
2SELECT AVG(commission) FROM employees;
3-- Calculation: (500 + 1200 + 800) / 3 = 833.33!
4-- The 2 employees with NULL are completely excluded from the denominator!

If your business requirement mandates treating missing commissions as ₹0.00:

1-- True average across all 5 employees:
2SELECT AVG(IFNULL(commission, 0.00)) FROM employees;
3-- Calculation: (500 + 0 + 1200 + 0 + 800) / 5 = 500.00!

5. Aggregating Unique Values with `DISTINCT`

You can combine DISTINCT inside an aggregate function:

1-- Count how many unique customers have placed orders:
2SELECT COUNT(DISTINCT customer_id) AS active_customer_count FROM orders;

6. Best Practices & Common Pitfalls

Mixing Scalar and Aggregate Columns Without GROUP BY: In strict SQL mode (ONLY_FULL_GROUP_BY), running SELECT department, AVG(salary) FROM employees; triggers an error! You cannot ask for a single average salary alongside multiple department names without a GROUP BY clause.
MIN and MAX on Dates and Strings: MIN() and MAX() work seamlessly on dates (finding oldest/newest) and alphabetical strings ('Aarav' to 'Zoya').

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does COUNT(*) count in a relational database table?

Question 2

What is the behavior of SUM() and AVG() when they encounter NULL values in a numeric column?

Question 3

If a table has 10 rows and 4 rows have rating = NULL, what does SELECT COUNT(rating) FROM table; return?

Question 4

Which query counts the total number of distinct cities represented in a customers table?

Question 5

What does MIN(order_date) return when executed against an orders table?

Next Lesson

Grouping Data with GROUP BY

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Control Flow Functions (IF, IFNULL, COALESCE)	Grouping Data with GROUP BY

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Grouping Data with GROUP BY

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

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Aggregate Functions: COUNT, SUM, AVG, MIN, and MAX

1. The Big 5 Aggregate Functions

Function	Purpose	Input Types	Treats NULL as...
`COUNT()`	Counts the number of rows or non-null values.	Any	`COUNT(*)` counts all rows; `COUNT(col)` ignores NULL.
`SUM()`	Calculates the total sum of values.	Numeric	Ignored (Skipped).
`AVG()`	Calculates the arithmetic mean.	Numeric	Ignored (Skipped).
`MIN()`	Finds the minimum value.	Numeric, Date, String	Ignored (Skipped).
`MAX()`	Finds the maximum value.	Numeric, Date, String	Ignored (Skipped).

2. Practical Aggregation Examples

1SELECT 
  COUNT(*) AS total_orders,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS average_order_value,
  MIN(total_amount) AS smallest_order,
  MAX(total_amount) AS largest_order,
  MIN(order_date) AS earliest_order,
  MAX(order_date) AS most_recent_order
9FROM orders;

3. The `COUNT(*)` vs `COUNT(column)` Distinction

One of the most critical concepts in SQL is the difference between COUNT(*) and COUNT(col):

 Table: employees (5 rows total)
 +----+----------+------------+
 | id | name     | commission |
 +----+----------+------------+
 |  1 | Aarav    | 500.00     |
 |  2 | Priya    | NULL       |
 |  3 | Rohan    | 1200.00    |
 |  4 | Diya     | NULL       |
 |  5 | Kabir    | 800.00     |
 +----+----------+------------+
11 
 SELECT COUNT(*) FROM employees;           --> Returns 5 (Counts total rows)
 SELECT COUNT(commission) FROM employees;  --> Returns 3 (Counts ONLY non-null values!)

4. How `AVG()` Handles NULL Values

Because aggregate functions skip NULL rows, AVG() computes the mean divided only by the count of non-null rows:

1-- In the table above, commission has values: 500, 1200, 800 (and 2 NULLs).
2SELECT AVG(commission) FROM employees;
3-- Calculation: (500 + 1200 + 800) / 3 = 833.33!
4-- The 2 employees with NULL are completely excluded from the denominator!

If your business requirement mandates treating missing commissions as ₹0.00:

1-- True average across all 5 employees:
2SELECT AVG(IFNULL(commission, 0.00)) FROM employees;
3-- Calculation: (500 + 0 + 1200 + 0 + 800) / 5 = 500.00!

5. Aggregating Unique Values with `DISTINCT`

You can combine DISTINCT inside an aggregate function:

1-- Count how many unique customers have placed orders:
2SELECT COUNT(DISTINCT customer_id) AS active_customer_count FROM orders;

6. Best Practices & Common Pitfalls

Mixing Scalar and Aggregate Columns Without GROUP BY: In strict SQL mode (ONLY_FULL_GROUP_BY), running SELECT department, AVG(salary) FROM employees; triggers an error! You cannot ask for a single average salary alongside multiple department names without a GROUP BY clause.
MIN and MAX on Dates and Strings: MIN() and MAX() work seamlessly on dates (finding oldest/newest) and alphabetical strings ('Aarav' to 'Zoya').

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does COUNT(*) count in a relational database table?

Question 2

What is the behavior of SUM() and AVG() when they encounter NULL values in a numeric column?

Question 3

If a table has 10 rows and 4 rows have rating = NULL, what does SELECT COUNT(rating) FROM table; return?

Question 4

Which query counts the total number of distinct cities represented in a customers table?

Question 5

What does MIN(order_date) return when executed against an orders table?

Next Lesson

Grouping Data with GROUP BY

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Control Flow Functions (IF, IFNULL, COALESCE)	Grouping Data with GROUP BY

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Aggregate Functions: COUNT, SUM, AVG, MIN, and MAX

1. The Big 5 Aggregate Functions

2. Practical Aggregation Examples

3. The COUNT(*) vs COUNT(column) Distinction

4. How AVG() Handles NULL Values

5. Aggregating Unique Values with DISTINCT

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Grouping Data with GROUP BY

Related Lessons

Practice Quiz

Loading Page Content...

Aggregate Functions: COUNT, SUM, AVG, MIN, and MAX

1. The Big 5 Aggregate Functions

2. Practical Aggregation Examples

3. The COUNT(*) vs COUNT(column) Distinction

4. How AVG() Handles NULL Values

5. Aggregating Unique Values with DISTINCT

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Grouping Data with GROUP BY

Related Lessons

Practice Quiz

3. The `COUNT(*)` vs `COUNT(column)` Distinction

4. How `AVG()` Handles NULL Values

5. Aggregating Unique Values with `DISTINCT`

3. The `COUNT(*)` vs `COUNT(column)` Distinction

4. How `AVG()` Handles NULL Values

5. Aggregating Unique Values with `DISTINCT`