MySQL COUNT(), AVG(), and SUM() Functions
MySQL provides several aggregate functions that allow you to perform calculations on a set of values and return a single value. The COUNT(), AVG(), and SUM() functions are among the most commonly used.
COUNT() Function
The COUNT() function returns the number of rows that match a specified condition.
Basic Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;This SQL command counts the number of rows where the condition is met.
Example: COUNT()
SELECT COUNT(*)
FROM employees
WHERE department = 'Sales';This SQL command counts the number of employees in the ‘Sales’ department.
AVG() Function
The AVG() function returns the average value of a numeric column.
Basic Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;This SQL command calculates the average value of the specified column where the condition is met.
Example: AVG()
SELECT AVG(salary)
FROM employees
WHERE department = 'Sales';This SQL command calculates the average salary of employees in the ‘Sales’ department.
SUM() Function
The SUM() function returns the total sum of a numeric column.
Basic Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;This SQL command calculates the sum of the specified column where the condition is met.
Example: SUM()
SELECT SUM(salary)
FROM employees
WHERE department = 'Sales';This SQL command calculates the total salary of employees in the ‘Sales’ department.
Using COUNT(), AVG(), and SUM() with GROUP BY
These functions can also be used with the GROUP BY clause to perform calculations on groups of data.
Example: COUNT() with GROUP BY
SELECT department, COUNT(*)
FROM employees
GROUP BY department;This SQL command counts the number of employees in each department.
Example: AVG() with GROUP BY
SELECT department, AVG(salary)
FROM employees
GROUP BY department;This SQL command calculates the average salary for each department.
Example: SUM() with GROUP BY
SELECT department, SUM(salary)
FROM employees
GROUP BY department;This SQL command calculates the total salary for each department.
Conclusion
The COUNT(), AVG(), and SUM() functions in MySQL are essential tools for performing aggregate calculations on your data. They can be used with various clauses to filter and group your data as needed.