Using MySQL Aggregate functions with GROUP BY

Normally, the SELECT statement is used to retrieve all matching records from one or more tables based on the different clauses used in the statement. But sometimes we need the summary type of data from the tables based on any field and aggregate function is used to do this type of task. For example, when any company needs the monthly sales report then the sales amount will need to be added based on the sales amount of each month to generate the report. Many aggregate functions exist in MySQL to do different types of summary tasks. Generally, the GROUP BY clause is used with each aggregate function. The functions of different MySQL aggregate functions and the uses of some common aggregate functions are shown in this article using two-sample MySQL database tables.

Syntax:

SELECT field1, fields2,..., fieldn, aggregate_function(fieldx)
FROM table
WHERE conditions
GROUP BY field1 , field2,...,,fieldn;

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.

CREATE TABLE salesperson (
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.

SELECT * FROM salesperson;

SELECT * FROM sales;

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.

SELECT area as City, COUNT(*) as `Total Sales Person`
FROM salesperson
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.

SELECT salesperson.name, SUM(amount) as `Total sales`

FROM salesperson, sales

WHERE salesperson.id = sales.salesperson_id

GROUP BY sales.salesperson_id;

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.

SELECT MONTH(sales.sales_date) as Month, MAX(amount) as `Maximum Sales`,
salesperson.name as `Sales Person`

FROM salesperson, sales

WHERE salesperson.id = sales.salesperson_id

GROUP BY MONTH(sales.sales_date), salesperson.name ;

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.

SELECT MONTH(sales.sales_date) as Month, GROUP_CONCAT(amount) as Sales,

SUM(amount) as `Total Sales`

FROM sales GROUP BY MONTH(sales.sales_date);

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.

from Linux Hint https://ift.tt/3elILV4