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)
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');
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.