MySQL provides various aggregate functions to perform or calculate the aggregated value, such as finding minimum value, maximum value, sum, etc. Generally, we use 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 Aggregate functions that are available to work.

Methods | Description |
---|---|

AVG() | It finds or returns the Average value. |

BIT_AND() | Returns bitwise AND |

BIT_OR() | It 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 find the Minimum value. |

STD() | The population standard deviation |

STDDEV() | Use this MySQL method to get the population standard deviation |

STDDEV_POP() | This returns the population standard deviation |

STDDEV_SAMP() | Returns the sample standard deviation |

SUM() | Use this to return or find the sum. |

VAR_POP() | This method returns the population standard variance. |

VAR_SAMP() | It returns the sample variance. |

VARIANCE() | This method returns the population standard variance. |

### MySQL Aggregate Functions Example

To demonstrate these Aggregate functions, we are going to use the below-shown data, which has 15 records.

In this example, we are going to use the AVG, SUM, MIN, and MAX methods 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 customer;

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

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

### Standard Deviation Example

In this example, we are going to use the STD, STDDEV, STDDEV_POP, and STDDEV_SAMP on the 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 customer;

This example is the same as above — however, this time, we used the Occupation column to group the customers. Next, we used the methods 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 customer GROUP BY Occupation;

### Variance Examples

In this example, we are going to use the MySQL aggregate functions like VARIANCE, VAR_POP, and VAR_SAMP on the 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 customer;

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 customer GROUP BY Occupation;

### Bitwise AND, OR, and XOR Example

We use the BIT_AND, BIT_OR, and BIT_XOR on the Yearly Income & Sales columns.

SELECT BIT_AND(YearlyIncome), BIT_OR(YearlyIncome), BIT_XOR(YearlyIncome) , BIT_AND(Sales), BIT_OR(Sales), BIT_XOR(Sales) FROM customer;

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 customer GROUP BY Occupation;

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

SELECT COUNT(*), COUNT(FirstName), COUNT(DISTINCT FirstName) FROM customer