MySQL Aggregate Function

This example illustrates how use the aggregate function in the MySQL Query.

In this example we use the aggregate function ('COUNT', 'MIN', 'MAX', and 'SUM') in the MySQL Query. The group (aggregate) functions that operate on set of values. Some times we need information that is not actually stored in the database but we can compute it by operating on some set of stored data. Aggregate functions can be used for this purpose.

The following are the Aggregate function:

Name	Description
AVG()	It Return the average value of the argument
BIT_AND()	It Return bitwise and
BIT_OR()	It Return bitwise or
BIT_XOR()	It Return bitwise xor
COUNT(DISTINCT)	It Return the count of a number of different values
COUNT()	It Return a count of the number of rows returned
GROUP_CONCAT()	It Return a concatenated string
MAX()	It Return the maximum value
MIN()	It Return the minimum value
STD()	It Return the population standard deviation
STDDEV_POP()	It Return the population standard deviation
STDDEV_SAMP()	It Return the sample standard deviation
STDDEV()	It Return the population standard deviation
SUM()	It Return the sum
VAR_POP()	It Return the population standard variance
VAR_SAMP()	It Return the sample variance
VARIANCE()	It Return the population standard variance

Table employee:

Query

 select * from employee;

Output

+----+--------+----------------------+--------+
| id | name   | designation          | salary |
+----+--------+----------------------+--------+
| 1  | Ajay   | programmer           | 105    |
| 2  | Vinay  | programmer           | 130    |
| 3  | Kamal  | sr. graphic designer | 101    |
| 4  | Rajiv  | graphic designer     | 120    |
| 5  | Nitin  | content writer       | 110    |
| 6  | Sachin | programmer           | 150    |
| 7  | Karan  | programmer           | 160    |
| 8  | Rajesh | graphic designer     | 50     |
+----+--------+----------------------+--------+

The 'COUNT' function is used to count the all rows of the table.

Query

 SELECT COUNT(*) FROM employee;

Output

+----------+
| COUNT(*) |
+----------+
| 8        |
+----------+

The 'MIN' function is used to find the minimum value of the specific field and the 'MAX' function is used to find the maximum value of the field.

Query

SELECT designation, MIN(salary), MAX(salary) FROM employee GROUP BY designation;

Output

+----------------------+-------------+-------------+
| designation          | MIN(salary) | MAX(salary) |
+----------------------+-------------+-------------+
| content writer       | 110         | 110         |
| graphic designer     | 50          | 120         |
| programmer           | 105         | 160         |
| sr. graphic designer | 101         | 101         |
+----------------------+-------------+-------------+

Now, The 'SUM' function is used to sum all the values of the specific column of the table.

Query

 select SUM(salary) FROM employee;

Output

+-------------+
| SUM(salary) |
+-------------+
| 926         |
+-------------+