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.

MySQL Aggregate Functions	Description
AVG()	It finds or returns the Average value.
BIT_AND()	Returns bitwise AND
BIT_OR()	This MySQL Aggregate function return 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 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 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, MAX functions on Yearly Income and Sales column. The following query will calculate 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 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 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 same as above. However, this time we used the Occupation column to group the customers. Next, we used the above mentioned functions 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

MySQL 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. This will 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 above mentioned VARIANCE 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 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 MySQL 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

MySQL Aggregate Functions Example 5

This example show you the COUNT, and COUNT(DISTINCT) Functions example. Here, we used both these function on 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

Thank You for Visiting Our Blog