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 | +-------------+
|