MySQL Aggregate Functions

MySQL provides various aggregate functions to perform or calculate the aggregated value, such as finding minimum value, maximum value, sum, etc.

Generally, we use MySQL Aggregate Functions along with Group By Clause to find the aggregated value of each group. However, you can use them without Group By clause.

MySQL Aggregate Functions

The following are the list of MySQL Aggregate functions that are available to work.

Aggregate Functions	Description
AVG()	It finds or returns the Average value.
BIT_AND()	Returns bitwise AND
BIT_OR()	This MySQL Aggregate function returns the bitwise OR value.
BIT_XOR()	Returns the Bitwise XOR value
COUNT()	Counts the number of rows returned.
COUNT(DISTINCT)	It counts the number of distinct or different values.
GROUP_CONCAT()	Returns the concatenated string
MAX()	Finds the Maximum value
MIN()	Use this MySQL Aggregate function to return or find the Minimum value.
STD()	Returns the population standard deviation
STDDEV()	Use this MySQL function to return the population standard deviation
STDDEV_POP()	This returns the population standard deviation
STDDEV_SAMP()	Returns the sample standard deviation
SUM()	Use this MySQL Aggregate function to return or find the sum.
VAR_POP()	This function returns the population standard variance.
VAR_SAMP()	It returns the sample variance.
VARIANCE()	This function returns the population standard variance.

MySQL Aggregate Functions Example 1

To demonstrate these Aggregate functions, we are going to use the below shown data

In this example, we are going to use the AVG, SUM, MIN, and MAX functions on the Yearly Income and Sales column. The following Aggregate query calculates the Sum, Average, Minimum, and Maximum of Yearly_Income column, and Sales column in a customer table.

SELECT   AVG(YearlyIncome),
         AVG(Sales),
         SUM(YearlyIncome),
         SUM(Sales),
         MIN(YearlyIncome),
         MIN(Sales),
         MAX(YearlyIncome),
         MAX(Sales)
 FROM `MySQL Tutorial`.customer;

OUTPUT

Generally, we use these MySQL Aggregate functions along with Group By Clause to group the items by category. Let us see the Example.

SELECT Occupation, 
       AVG(YearlyIncome),
       AVG(Sales),
       SUM(YearlyIncome),
       SUM(Sales),
       MIN(YearlyIncome),
       MIN(Sales),
       MAX(YearlyIncome),
       MAX(Sales)
 FROM `MySQL Tutorial`.customer
 GROUP BY Occupation;

OUTPUT

MySQL Aggregate Functions Example 2

In this example, we are going to use the MySQL STD, STDDEV, STDDEV_POP, STDDEV_SAMP functions on Yearly Income and Sales column to find the standard deviation of both these columns.

SELECT  STD(YearlyIncome),
        STDDEV(YearlyIncome),
        STDDEV_POP(YearlyIncome) AS STDDEV_POP_Income,
        STDDEV_SAMP(YearlyIncome) AS STDDEV_SAMP_Income,
        STD(Sales),
        STDDEV(Sales),
        STDDEV_POP(Sales),
        STDDEV_SAMP(Sales)
 FROM `MySQL Tutorial`.customer;

OUTPUT

This example is the same as above — however, this time, we used the Occupation column to group the customers. Next, we used the functions as mentioned above to find the standard deviation.

SELECT  Occupation,
        STD(YearlyIncome),
        STDDEV(YearlyIncome),
        STDDEV_POP(YearlyIncome) AS STDDEV_POP_Income,
        STDDEV_SAMP(YearlyIncome) AS STDDEV_SAMP_Income,
        STD(Sales),
        STDDEV(Sales),
        STDDEV_POP(Sales),
        STDDEV_SAMP(Sales)
 FROM `MySQL Tutorial`.customer
 GROUP BY Occupation;

OUTPUT

Aggregate Functions Example 3

In this example, we are going to use the MySQL aggregate function like VARIANCE, VAR_POP, VAR_SAMP functions on Yearly Income, and Sales column. They find the standard variance of both these columns.

SELECT  VAR_POP(YearlyIncome),
        VARIANCE(YearlyIncome),
        VAR_SAMP(YearlyIncome) ,
        VAR_POP(Sales),
        VARIANCE(Sales),
        VAR_SAMP(Sales)
 FROM `MySQL Tutorial`.customer;

OUTPUT

In this example, we used the Occupation column to group the customers. Next, we used the VARIANCE as mentioned earlier functions to find the standard variance of each group.

SELECT  Occupation,
        VAR_POP(YearlyIncome),
        VARIANCE(YearlyIncome),
        VAR_SAMP(YearlyIncome) ,
        VAR_POP(Sales),
        VARIANCE(Sales),
        VAR_SAMP(Sales)
 FROM `MySQL Tutorial`.customer
 GROUP BY Occupation;

OUTPUT

MySQL Aggregate Functions Example 4

In this example, we use the MySQL BIT_AND, BIT_OR, BIT_XOR functions on Yearly Income & Sales columns.

SELECT  BIT_AND(YearlyIncome),
        BIT_OR(YearlyIncome),
        BIT_XOR(YearlyIncome) ,
        BIT_AND(Sales),
        BIT_OR(Sales),
        BIT_XOR(Sales)
 FROM `MySQL Tutorial`.customer;

OUTPUT

Here, we used the Group By clause to group the Occupation column in the customers table. Next, we used the above mentioned Aggregate functions.

SELECT  Occupation,
        BIT_AND(YearlyIncome),
        BIT_OR(YearlyIncome),
        BIT_XOR(YearlyIncome) ,
        BIT_AND(Sales),
        BIT_OR(Sales),
        BIT_XOR(Sales)
 FROM `MySQL Tutorial`.customer
 GROUP BY Occupation;

OUTPUT

Aggregate Functions Example 5

This example shows you the MySQL COUNT, and COUNT(DISTINCT) Functions example. Here, we used both these functions on the First name column. As you can see, COUNT(DISTINCT) Function is returning the count of distinct or unique records in this column.

SELECT  COUNT(*),
        COUNT(FirstName),
        COUNT(DISTINCT FirstName)
 FROM `MySQL Tutorial`.customer

OUTPUT