Aggregation Function in MySQL

There are number of functions available in MySQL library, in this particular article we will  discuss aggregation function
Aggregation function s are generally used with SELECT statements containing GROUP BY clauses, however these may be used without group by clauses as well.
Below table describes aggregation functions available in MySQL (including MySQL 8.0 version)

Name	Description
AVG()	Returns average value of argument
BIT_AND()	Return bitwise AND
BIT_OR()	Return bitwise OR
BIT_XOR()	Return bitwise XOR
COUNT()	Return a count of rows
COUNT(DISTINCT)	Return a count of number of different values
GROUP_CONCAT()	Returns concatenated string
JSON_ARRAYAGG()	Return result set as single array
JSON_OBJECTAGG()	Return result set as a single object
MAX()	Returns maximum value
MIN()	Returns minimum value
STD(), STDDEV(), STDDEV_POP()	Returns the population standard  deviation
STDDEV_SAMP()	Returns the sample standard deviation
SUM()	Return the summation
VAR_POP()	Return population standard variance
VAR_SAMP()	Returns sample variance
VARIANCE()	Returns Population standard variance

In below section, we will discuss few of these with examples, 
first we will create table agg_tut for use with few records in it.

CREATE TABLE agg_tut (
emp_id int NOT NULL,
emp_name varchar(255),
salary double,
itax double,
age int
);

insert into agg_tut values ('101','John','12500','0','25');
insert into agg_tut values ('102','Smith','52500','2500','45');
insert into agg_tut values ('103','Jinh','22500','2200','32');
insert into agg_tut values ('104','Mist','15500','1000','23');
insert into agg_tut values ('105','Suki','13000','1200','28');
insert into agg_tut values ('108','Rock','33000','1800','30');

count ()

select count of all rows present in table

select count(*) from agg_tut;

+----------+

| count(*) |

+----------+

| 6        |

+----------+

min() and max()

Apply min() and max() on emp_id column first

select min(emp_id) from agg_tut;

+-------------+

| min(emp_id) |

+-------------+

| 101         |

+-------------+

select max(emp_id) from agg_tut;

+-------------+

| max(emp_id) |

+-------------+

| 108         |

+-------------+

Now apply min(), min() on salary column to rectify how much company is paying

select min(salary) from agg_tut;

+-------------+

| min(salary) |

+-------------+

| 12500       |

+-------------+

select max(salary) from agg_tut;

+-------------+

| max(salary) |

+-------------+

| 52500       |

+-------------+

Now calculate the average using avg() salary of employee in organization

select avg(salary) from agg_tut;

+-------------------------+

| avg(salary)             |

+-------------------------+

| 24833.33333333332       |

+-------------------------+

Now calculate the total salary of employee using sum(), so organization will have estimation towards employee cost

select sum(salary) from agg_tut;

+--------------+

| sum(salary)  |

+--------------+

| 149000       |

+--------------+

JSON_OBJECTAGG

Consider we have below data in employee table

select * from agg_tut;

+--------+-----------+--------+-------+------+

| emp_id | emp_name  | salary |itax   |age   |

+--------+-----------+--------+-------+------+

| 101    | John      |12500   |0      |25    |

| 102    | Smith     |52500   |2500   |45    |

| 103    | Jinh      |22500   |2200   |32    |

| 104    | Mist      |15500   |1000   |23    |

| 105    | Suki      |13000   |1200   |28    |

| 108    | Rock      |33000   |1800   |30    |

+--------+-----------+--------+-------+------+

as we know JSON is key value pair language , consider emp_id a as key and other columns such as emp_name and salary are value

Select emp_id,JSON_OBJECTAGG(emp_name,salary) from agg_tut group by emp_id;

+--------+---------------------+

| emp_id | JSON_OBJECTAGG      |

+--------+---------------------+

| 101    | {“John” : 2500.0    |

| 102    | {“Smith” : 52500.0  |

| 103    | {“Jinh” : 22500.0   |

| 104    | {“Mist” : 15500.0   |

| 105    | {“Suki” : 13000.0   |

| 108    | {“Rock” : 33000.0   |

+--------+---------------------+

We have discussed, daily used aggregation function, as described in first table, there are number of aggregation functions available in MySQL.