COUNT(), AVG(), SUM()

Learning Focus

Use this lesson to understand COUNT(), AVG(), SUM() with practical syntax and examples.

Concept Overview

What Are Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. In MySQL, three essential aggregate functions are:

• COUNT() - Counts the number of rows
• AVG() - Calculates the average of a numeric column
• SUM() - Computes the total sum of a numeric column

Why Are They Important?

Aggregate functions are foundational in data analysis and reporting. They help transform raw data into actionable insights by summarizing vast tables into digestible numbers.

Where Do They Fit?

They are typically used in SELECT statements, often combined with the GROUP BY clause, and play a key role in:

• Business reporting
• Dashboard metrics
• Data audits and validation

---

Basic Syntax & Rules

SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
[WHERE condition]
[GROUP BY column_name];

Parameters:

• AGGREGATE_FUNCTION: COUNT(), AVG(), SUM()
• column_name: The column to perform the calculation on
• WHERE: (Optional) Filters the rows to be aggregated
• GROUP BY: (Optional) Divides rows into groups

Key Considerations:

• NULL values are ignored by AVG() and SUM() but counted (or not) differently in COUNT().
• You can combine multiple aggregate functions in one query.
• Always ensure you're aggregating numeric data for AVG() and SUM().

---

Step-by-Step Examples

Sample Table: employees

id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	60000
3	Charlie	HR	40000
4	David	HR	42000
5	Eva	IT	NULL

---

Example 1: Count all employees

SELECT COUNT(*) AS total_employees
FROM employees;

Result:

total_employees

---

5

---

---

Example 2: Count only those with a salary

SELECT COUNT(salary) AS salary_count
FROM employees;

Result:

salary_count

---

4

---

---

Example 3: Average salary

SELECT AVG(salary) AS average_salary
FROM employees;

Result:

average_salary

---

48000.00

---

---

Example 4: Sum salary by department

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Result:

department	total_salary
Sales	110000
HR	82000
IT	NULL

---

Example 5: Count employees by department

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Result:

department	employee_count
Sales	2
HR	2
IT	1

---

Practical Use Cases

1. Sales Reporting

• Use SUM() to calculate total revenue.
• Use COUNT() to get total orders.

2. Employee Analysis

• AVG(salary) for budgeting.
• COUNT(*) to determine department sizes.

3. Inventory Management

• SUM(stock) to get total inventory.
• AVG(price) to find average product price.

4. Customer Engagement

• COUNT(*) to analyze how many customers signed up monthly.

5. Performance Metrics

• AVG(response_time) for system performance dashboards.

---

Common Mistakes & Troubleshooting

Mistake	Explanation	Solution
Using AVG() on non-numeric columns	Causes error or meaningless result	Use only on numeric fields
Counting with COUNT(column) when column has NULL	NULLs are excluded	Use COUNT(*) to include all rows
Forgetting GROUP BY	Causes grouping errors when combining fields	Use GROUP BY when selecting non-aggregated columns
Incorrect aliasing	Output column is unclear	Always alias results for clarity: AS total_sales

---

Best Practices

• Prefer COUNT(*) for total row count; COUNT(column) only when ignoring NULLs.
• Use GROUP BY carefully-always include all selected non-aggregated columns.
• Index fields used in WHERE clause to improve aggregation performance.
• Avoid aggregating on very large datasets without filters; use LIMIT during testing.

---

Hands-On Practice

Sample Data Setup

CREATE TABLE sales (
    id INT,
    product VARCHAR(50),
    quantity INT,
    price DECIMAL(10,2)
);

INSERT INTO sales VALUES
(1, 'Book', 3, 15.00),
(2, 'Pen', 10, 1.50),
(3, 'Notebook', 5, 7.25),
(4, 'Book', 2, 15.00),
(5, 'Pen', NULL, 1.50);

---

Exercise 1: Count total sales transactions

SELECT COUNT(*) FROM sales;
-- Answer: 5

---

Exercise 2: Find average quantity sold (ignore NULLs)

SELECT AVG(quantity) FROM sales;
-- Answer: 5.0

---

Exercise 3: Total revenue per product

SELECT product, SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY product;
-- Expected:
-- | Book     | 75.00 |
-- | Pen      | 15.00 |
-- | Notebook | 36.25 |

---

Connection to Other Concepts

• Prerequisite Concepts:
  • SELECT statements
  • WHERE clause
  • Basic GROUP BY
• Related Concepts:
  • HAVING: Filtering grouped results
  • ORDER BY: Sorting aggregated data
  • JOIN: Aggregating across multiple tables
• What to Learn Next:
  • GROUP BY with HAVING
  • Window functions (OVER())
  • Subqueries with aggregation

---

Concept Map

flowchart LR
    A[Schema Context] --> B[COUNT(), AVG(), SUM()]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

SELECT COUNT(*) AS total_rows FROM table_name;
SELECT AVG(score) AS avg_score FROM exams;
SELECT SUM(amount) AS total_revenue FROM invoices;

What's Next

• Previous: MIN(), MAX() - Review the previous lesson to reinforce context.
• Module Overview - Return to this module index and choose another related lesson.