AGGREGATE FUNCTIONS

Summary: in this tutorial, you will learn about MySQL aggregate functions including AVG, COUNT, SUM, MAX and MIN.

An aggregate function performs a calculation on multiple values and returns a single value.

For example, you can use the AVG() aggregate function that takes multiple numbers and returns the average value of the numbers.

The following illustrates the syntax of an aggregate function:

aggregate_function(DISTINCT | ALL expression)

In this syntax:

• First, specify the name of the aggregate function e.g., AVG(). See the list of aggregate functions in the following section.
• Second, use DISTINCT if you want to calculate based on distinct values or ALL in case you want to calculate all values including duplicates. The default is ALL.
• Third, specify an expression that can be a column or an expression that involves column and arithmetic operators.

The aggregate functions are often used with the GROUP BY clause to calculate an aggregate value for each group e.g., the average value by the group or the sum of values in each group.

The following picture illustrates the SUM() aggregate function is used in conjunction with a GROUP BY clause:

Aggregate functions in MySQL are powerful SQL tools used to perform calculations on a set of values and return a single value. These functions are commonly used with the GROUP BY clause to group data into subsets before performing operations, but they can also be used without grouping.

List of Common Aggregate Functions

1. COUNT() - Counts the number of rows.
2. SUM() - Calculates the sum of values.
3. AVG() - Returns the average value.
4. MIN() - Retrieves the minimum value.
5. MAX() - Retrieves the maximum value.
6. GROUP_CONCAT() - Concatenates non-NULL values into a string.
7. VARIANCE() / VAR_POP() / VAR_SAMP() - Calculates variance.
8. STD() / STDDEV() / STDDEV_POP() / STDDEV_SAMP() - Calculates standard deviation.
9. BIT_AND() - Bitwise AND for a set of values.
10. BIT_OR() - Bitwise OR for a set of values.
11. BIT_XOR() - Bitwise XOR for a set of values.

Now let's dive into each one of these with examples.

1. COUNT()

The COUNT() function returns the number of rows that match a specified condition.

COUNT(expression)

Example:

Count all rows in a table:

SELECT COUNT(*) AS total_rows 
FROM employees;

Count rows where a condition is met (e.g., non-NULL salaries):

SELECT COUNT(salary) AS total_salaries 
FROM employees;

Count distinct values in a column:

SELECT COUNT(DISTINCT department) AS total_departments 
FROM employees

2. SUM()

The SUM() function adds all values in a numeric column

SUM(expression)

Example:

Sum up all salaries in the employees table:

SELECT SUM(salary) AS total_salary 
FROM employees;

Sum of salaries grouped by department:

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

3. AVG()

The AVG() function calculates the average value of a numeric column.

AVG(expression)

Example:

Find the average salary:

SELECT AVG(salary) AS average_salary 
FROM employees;

Find the average salary for each department:

SELECT department, 
AVG(salary) AS average_salary 
FROM employees 
GROUP BY department;

4. MIN()

The MIN() function retrieves the smallest value from a column.

MIN(expression)

Example:

Find the lowest salary:

SELECT MIN(salary) AS minimum_salary 
FROM employees;

Find the lowest salary in each department:

SELECT department, 
MIN(salary) AS minimum_salary 
FROM employees 
GROUP BY department;

5. MAX()

The MAX() function retrieves the largest value from a column.

MAX(expression)

Example:

Find the highest salary:

SELECT MAX(salary) AS maximum_salary 
FROM employees;

Find the highest salary for each department:

SELECT department, MAX(salary) AS maximum_salary 
FROM employees 
GROUP BY department;

6. GROUP_CONCAT()

The GROUP_CONCAT() function concatenates values from multiple rows into a single string.

GROUP_CONCAT(expression [ORDER BY expression] [SEPARATOR string])

Example:

Concatenate employee names into a comma-separated list:

SELECT GROUP_CONCAT(employee_name SEPARATOR ', ') AS employee_list 
FROM employees;

Concatenate employee names grouped by department:

SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employee_list 
FROM employees 
GROUP BY department;

7. VARIANCE(), VAR_POP(), VAR_SAMP()

These functions calculate variance of a set of values. VARIANCE() is synonymous with VAR_POP() (population variance), while VAR_SAMP() computes the sample variance.

VARIANCE(expression)   -- Same as VAR_POP()
VAR_POP(expression)
VAR_SAMP(expression)

Example:

Population variance of salaries:

SELECT VAR_POP(salary) AS variance_salary 
FROM employees;

Sample variance of salaries:

SELECT VAR_SAMP(salary) AS sample_variance_salary 
FROM employees;

8. STD(), STDDEV(), STDDEV_POP(), STDDEV_SAMP()

These functions compute the standard deviation of a set of values. STD() is a synonym for STDDEV_POP() (population standard deviation), and STDDEV_SAMP() computes the sample standard deviation.

STD(expression)         -- Same as STDDEV_POP()
STDDEV(expression)
STDDEV_POP(expression)
STDDEV_SAMP(expression)

Example:

Population standard deviation of salaries:

SELECT STDDEV_POP(salary) AS population_stddev_salary 
FROM employees;

Sample standard deviation of salaries:

SELECT STDDEV_SAMP(salary) AS sample_stddev_salary 
FROM employees;

9. BIT_AND()

The BIT_AND() function performs a bitwise AND across all values of a set.

BIT_AND(expression)

Example:

Perform a bitwise AND on all employee IDs:

SELECT BIT_AND(employee_id) AS bitwise_and_result 
FROM employees;

10. BIT_OR()

The BIT_OR() function performs a bitwise OR across all values of a set.

BIT_OR(expression)

Example:

Perform a bitwise OR on all employee IDs:

SELECT BIT_OR(employee_id) AS bitwise_or_result 
FROM employees;

11. BIT_XOR()

The BIT_XOR() function performs a bitwise XOR across all values of a set.

sql
Copy code
BIT_XOR(expression)

Example:

Perform a bitwise XOR on all employee IDs:

SELECT BIT_XOR(employee_id) AS bitwise_xor_result 
FROM employees;

Using Aggregate Functions with GROUP BY

To utilize aggregate functions more effectively, they are often used with the GROUP BY clause, which groups rows that have the same values in specified columns and performs calculations on each group.

Example:

Find the total salary for each department:

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

Find the average, minimum, and maximum salaries for each department:

SELECT department,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department;

Aggregate Functions and HAVING Clause

The HAVING clause is used to filter records after aggregate functions are applied, unlike WHERE which is used before aggregation.

Example:

Find departments with a total salary above 500,000:

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

Conclusion

Aggregate functions in MySQL are essential for summarizing and analyzing large datasets. They simplify complex queries by performing calculations like counting, summing, averaging, and more. Using them alongside GROUP BY and HAVING allows you to further refine and filter your data.