In MySQL, the COUNT(), AVG(), and SUM() functions are aggregate functions used to perform calculations on sets of rows. Here's how each function works:
The COUNT() function returns the number of rows that match a specified condition.
Syntax:
SELECT COUNT(column_name) FROM table_name WHERE condition;
Example:
SELECT COUNT(*) FROM employees;
This query will return the total number of rows in the `employees` table.
The AVG() function returns the average value of a numeric column.
Syntax:
SELECT AVG(column_name) FROM table_name WHERE condition;
Example:
SELECT AVG(salary) FROM employees;
This query will return the average salary of all employees in the `employees` table.
The SUM() function returns the total sum of values in a numeric column.
Syntax:
SELECT SUM(column_name) FROM table_name WHERE condition;
Example:
SELECT SUM(salary) FROM employees;
This query will return the total sum of salaries for all employees in the `employees` table.
You can also use these aggregate functions with the GROUP BY clause to perform calculations for each group.
Example:
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
This query will return the count, average salary, and total salary for each department in the `employees` table.
These aggregate functions are useful for various analytical purposes, such as calculating statistics, summarizing data, and generating reports.