COUNT, AVG, and SUM in MySQL

These are aggregate functions used in MySQL to perform calculations on data in a table.

1. COUNT() – Counting Rows

• Returns the number of rows that match a condition.
• Ignores NULL values.

Syntax

SELECT COUNT(column_name) FROM table_name WHERE condition;

Example: Count total employees

SELECT COUNT(*) FROM employees;

✅ Counts all employees.

Example: Count employees in the IT department

SELECT COUNT(*) FROM employees WHERE department = 'IT';

Example: Count employees with a salary

SELECT COUNT(salary) FROM employees;

✅ Ignores rows where salary is NULL.

2. AVG() – Calculating Average

• Returns the average (mean) of a numeric column.
• Ignores NULL values.

Syntax

SELECT AVG(column_name) FROM table_name WHERE condition;

Example: Find the average salary

SELECT AVG(salary) FROM employees;

Example: Find the average salary in IT

SELECT AVG(salary) FROM employees WHERE department = 'IT';

3. SUM() – Total Sum

• Returns the sum of values in a numeric column.
• Ignores NULL values.

Syntax

SELECT SUM(column_name) FROM table_name WHERE condition;

Example: Find the total salary of all employees

SELECT SUM(salary) FROM employees;

Example: Find total salary in the IT department

SELECT SUM(salary) FROM employees WHERE department = 'IT';

4. Using COUNT, AVG, and SUM Together

You can combine them in a single query.

Example: Get employee count, total salary, and average salary

SELECT     COUNT(*) AS total_employees,     SUM(salary) AS total_salary,     AVG(salary) AS avg_salaryFROM employees;

✅ Output:

+----------------+-------------+------------+| total_employees | total_salary | avg_salary |+----------------+-------------+------------+| 50             | 2,500,000    | 50,000     |+----------------+-------------+------------+

5. GROUP BY with COUNT, AVG, SUM

Use GROUP BY to apply these functions to each group separately.

Example: Get department-wise statistics

SELECT department,     COUNT(*) AS total_employees,     SUM(salary) AS total_salary,     AVG(salary) AS avg_salaryFROM employeesGROUP BY department;

✅ Output:

+------------+----------------+-------------+------------+| department | total_employees | total_salary | avg_salary |+------------+----------------+-------------+------------+| IT         | 20             | 1,200,000    | 60,000     || HR         | 10             | 500,000      | 50,000     || Finance    | 15             | 750,000      | 50,000     |+------------+----------------+-------------+------------+

Key Points

✅ COUNT(*) counts all rows.
✅ AVG(column_name) calculates the average (ignores NULL).
✅ SUM(column_name) calculates the total sum.
✅ Use GROUP BY for grouped calculations.