### Syntax:

Here, the summary value of **fieldx** column will be calculated based on the columns mentioned on the GROUP BY clause.

**List of MySQL aggregate functions:**

Aggregate Function |
Description |

COUNT() | It is used to count the total number of rows returned. |

COUNT(DISTINCT) | It is used to count the total number of unique rows returned. |

SUM() | It is used to calculate the sum of any numeric field values. |

MAX() | It is used to find out the maximum value of a field. |

MIN() | It is used to find out the minimum value of a field. |

AVG() | It is used to find out the average value of a field. |

BIT_OR() | It is used to return bit-wise OR value of a field. |

BIT_AND() | It is used to return bit-wise AND value of a field. |

BIT_XOR() | It is used to return bit-wise XOR value of a field. |

GROUP_CONCAT() | It is used to return the concatenated value of a field. |

JSON_ARRAYAGG() | It is used to return a JSON array of a field value. |

JSON_OBJECTAGG() | It is used to return a JSON object of a field value. |

STD() | It is used to return the population standard deviation. |

STDDEV() | It is used to return the population standard deviation. |

STDDEV_POP() | It is used to return the population standard deviation. |

STDDEV_SAMP() | It is used to return the sample standard deviation. |

VAR_POP() | It is used to return the population standard variance. |

VAR_SAMP() | It is used to return the sample variance. |

VARIANCE() | It is used to return the population standard variance. |

Create two related tables named **salesperson** and **sales** by running the following CREATE statements. These two tables are related by **id** field of **salesperson** table and **salesperson_id** field of **sales** table.

id INT(5) AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

mobile_no VARCHAR(50) NOT NULL,

area VARCHAR(50) NOT NULL,

email VARCHAR(50) NOT NULL) ENGINE = INNODB;

CREATE TABLE sales (

id INT(11) AUTO_INCREMENT PRIMARY KEY

sales_date date,

salesperson_id INT(5) NOT NULL,

amount INT(11),

FOREIGN KEY (salesperson_id) REFERENCES salesperson(id))

ENGINE = INNODB;

# Insert some records in both tables by running the following INSERT statements.

INSERT INTO salesperson values

(NULL, 'Jony', '0176753325' , 'California', 'jony@gmail.com'),

(NULL, 'Janifer', '0178393995', 'Texas', 'janifer@gmail.com'),

(NULL, 'Jubair', '01846352443' , 'Florida', 'jubair@gmail.com'),

(NULL, 'Albert', '01640000344' , 'Texas', 'albert@gmail.com');

INSERT INTO sales values

(NULL, '2020-02-11', 1, 10000),

(NULL, '2020-02-23', 3, 15000),

(NULL, '2020-03-06', 4, 7000),

(NULL, '2020-03-16' , 2 , 9000),

(NULL, '2020-03-23', 3, 15000),

(NULL, '2020-03-25', 4, 7000),

(NULL, '2020-03-27' , 2 , 8000),

(NULL, '2020-03-28', 4, 5000),

(NULL, '2020-03-29' , 2 , 3000),

(NULL, '2020-03-30', 3 , 7000);

Now, run the following statements to check the records of both **salesperson** and **sales** tables.

The uses of some commonly-used aggregate functions are shown in the next part of this article.

### Use of COUNT() function:

salesperson table contains area wise salesperson information. If you want to know the total number of the salesperson in each area the following SQL statement can be used. It will count the total number of salesperson from **salesperson** table group by **area**.

The following output will appear according to the table data.

### Use of SUM() function:

When it is required to know the total sales amount of each salesperson then the following SQL statement can be used to find out the total sales amount with the name of each salesperson from **salesperson** and **sales** table using SUM() function. ‘**salesperson_id**’ of **sales** table is used here for grouping.

The following output will appear after running the above statement. There are four salespersons in **salesperson** table and the output shows the total sales **amount** for each salesperson.

### Use of MAX() function:

When it is required to find out the monthly maximum sales based on each salesperson then the following SQL statement can be used to get the output. Here, MONTH() function is used to identify each month and MAX() function is used to find out the maximum amount value of each month from **sales** table.

The following output will appear after running the statement.

### Use of GROUP_CONCAT() function:

When it will require to find out the total sales amount based on each month by mentioning each unique sales amount of each month then the following SQL statement can be used. Here, MONTH() function is used to read monthly sales amount values based on the **sales_date** and GROUP_CONCAT() function is used to count the monthly sales amount.

The following output will appear after running the statement.

### Conclusion:

Aggregate functions help MySQL users to find out the different types of summary data easily by writing a simple query. The uses of four useful aggregate functions are explained in this article to help the readers know how aggregate functions used in MySQL.