MySQL Aggregate Functions
Summary: in this tutorial, you will learn about MySQL aggregate functions including AVG
COUNT
, SUM
, MAX
and MIN.
Introduction to MySQL aggregate functions
An aggregate function performs a calculation on multiple values and returns a single value.
For example, you can use the AVG()
an aggregate function that takes multiple numbers and returns the average value of the numbers.
The following illustrates the syntax of an aggregate function:
function_name(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 orALL
in case you want to calculate all values including duplicates. The default isALL
. - Third, specify an expression that can be a column or expression which 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()
an aggregate function is used in conjunction with a GROUP BY
clause:
MySQL supports the following aggregate functions:
Aggregate function | Description |
---|---|
AVG() | Return the average of non-NULL values. |
BIT_AND() | Return bitwise AND. |
BIT_OR() | Return bitwise OR. |
BIT_XOR() | Return bitwise XOR. |
COUNT() | Return the number of rows in a group, including rows with NULL values. |
GROUP_CONCAT() | Return a concatenated string. |
JSON_ARRAYAGG() | Return result set as a single JSON array. |
JSON_OBJECTAGG() | Return result set as a single JSON object. |
MAX() | Return the highest value (maximum) in a set of non-NULL values. |
MIN() | Return the lowest value (minimum) in a set of non-NULL values. |
STDEV() | Return the population standard deviation. |
STDDEV_POP() | Return the population standard deviation. |
STDDEV_SAMP() | Return the sample standard deviation. |
SUM() | Return the summation of all non-NULL values a set. |
VAR_POP() | Return the population standard variance. |
VARP_SAM() | Return the sample variance. |
VARIANCE() | Return the population standard variance. |
MySQL aggregate function examples
We will use the products
and orderdetails
tables from the sample database for demonstration:
MySQL aggregate function – AVG()
function examples
The AVG()
the function calculates the average value of a set of values. It ignores NULL in the calculation.
AVG(expression)
For example, you can use the AVG
function to calculate the average buy price of all products in the products
table by using the following query:
SELECT
AVG(buyPrice) average_buy_price
FROM
products;
The following example uses the AVG()
function to calculate the average buy price for each product line:
SELECT
productLine,
AVG(buyPrice)
FROM
products
GROUP BY productLine
ORDER BY productLine;
MySQL aggregate function – COUNT()
function examples
The COUNT()
the function returns the number of the value in a set.
For example, you can use the COUNT()
function to get the number of products in the products
table as shown in the following query:
SELECT
COUNT(*) AS total
FROM
products;
The following statement uses the COUNT()
function with the GROUP BY
clause to get the number of products for each product line:
SELECT
productLine,
COUNT(*)
FROM
products
GROUP BY productLine
ORDER BY productLine;
MySQL aggregate function – SUM()
function examples
The SUM()
the function returns the sum of values in a set. The SUM()
function ignores NULL
. If no matching row is found, then SUM()
function returns NULL.
To get the total order value of each product, you can use the SUM()
function in conjunction with the GROUP BY
clause as follows:
SELECT
productCode,
SUM(priceEach * quantityOrdered) total
FROM
orderDetails
GROUP BY productCode
ORDER BY total DESC;
To see the result in more detail, you can join the orderdetails
table to the products
table as shown in the following query:
SELECT
productCode,
productName,
SUM(priceEach * quantityOrdered) total
FROM
orderDetails
INNER JOIN
products USING (productCode)
GROUP BY productCode
ORDER BY total;
MySQL aggregate function – MAX()
function examples
The MAX()
the function returns the maximum value in a set.
MAX(expression)
For example, you can use the MAX()
function to get the highest buy price from the products
table as shown in the following query:
SELECT
MAX(buyPrice) highest_price
FROM
products;
The following statement uses the MAX()
function with the GROUP BY
clause to get the highest price per product line:
SELECT
productLine, MAX(buyPrice)
FROM
products
GROUP BY productLine
ORDER BY MAX(buyPrice) DESC;
MySQL aggregate function – MIN()
function examples
The MIN()
the function returns the minimum value in a set of values.
MIN(expression)
For example, the following query uses the MIN()
function to find the lowest price from the products
table:
SELECT
MIN(buyPrice) lowest_price
FROM
products;
The following example uses the MIN()
function with the GROUP BY
clause to get the lowest price per product line:
SELECT
productLine,
MIN(buyPrice)
FROM
products
GROUP BY productLine
ORDER BY MIN(buyPrice);
MySQL aggregate function – GROUP_CONCAT()
function example
The GROUP_CONCAT()
concatenates a set of strings and returns the concatenated string. See the following employees
and customers
tables:
The following statement uses the GROUP_CONCAT()
function to return the sales staff and list of customers that each sales staff is in charge of:
SELECT
firstName,
lastName,
GROUP_CONCAT(
DISTINCT customername
ORDER BY customerName) customers
FROM
employees
INNER JOIN customers
ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;
In this tutorial, you have learned how to use the most commonly used MySQL aggregate functions
0 Comments
CAN FEEDBACK
Emoji