MySQL Group By Clause and COUNT() Function
Mục Lục
Group By Clause:
The data can be retrieved from the MySQL database tables using the SELECT query in different ways. Generally, the Group By clause is used with the SELECT query to retrieve the set of rec-ords by grouping one or more column values. Many aggregate functions of MySQL are also used with the Group By clause to read data from the table, such as COUNT(), MAX(), MIN(), AVG(), etc. The uses of the Group By Clause with or without the COUNT() function have been discussed in this tutorial.
It is mainly used to get the summary of the table’s data based on the column(s) of the table. The syntax of this clause is provided below:
Syntax:
SELECT statements…
GROUP BY column1[,column2,…] ;
The SELECT query will retrieve the data from the tables based on the column names defined with the GROUP BY clause.
COUNT() Function:
This function counts the total number of records returned by executing the SELECT query. It returns a BIGINT value when one or more records are returned by the query. Otherwise, it re-turns 0. The syntax of the COUNT() function is provided. This function can be used in three dif-ferent ways which are explained below:
- COUNT(*)
It is used to count the total number of rows returned by the SELECT query by counting NULL, NOT NULL, and duplicate values. - COUNT(expression)
It is used to count the total number of rows returned by the SELECT query without counting the NULL values. - COUNT(distinct expression)
It is used to count the total number of rows returned by the SELECT query without counting the NULL values and duplicate values.
Uses of Group By Clause and COUNT() Function:
You have to create a database table with data in a MySQL database to check the GROUP By in MySQL. Open the terminal and connect with the MySQL server by executing the following command:
$
sudo
mysql
-u
root
Run the following command to create a database named test_db:
CREATE
DATABASE
test_db;
Run the following command to select the database:
USE
test_db;
Run the following query to create a table named sales_persons with four fields:
CREATE
TABLE
sales_persons
(
id
INT
AUTO_INCREMENT
PRIMARY
KEY
,
name
VARCHAR
(
30
)
NOT
NULL
,
VARCHAR
(
50
)
,
contact_no
VARCHAR
(
30
)
)
;
Run the following query to insert the three records into the sales_person table:
INSERT
INTO
`sales_persons`
(
`id`
,
`name`
,
`email`
,
`contact_no`
)
VALUES
(
NULL
,
‘Kamal Hasan’
,
,
‘0191275634’
)
,
(
NULL
,
‘Nila Hossain’
,
,
‘01855342357’
)
,
(
NULL
,
‘Abir Hossain’
,
,
‘01634235698’
)
;
Run the following query to create a table named sales with four fields that contains a foreign key that will create the one-to-many relationship from the sales_persons table to the sales table.
CREATE
TABLE
sales
(
id
INT
NOT
NULL
PRIMARY
KEY
,
sales_date
DATE
NOT
NULL
,
amount
INT
,
sp_id
INT
,
CONSTRAINT
fk_sp
FOREIGN
KEY
(
sp_id
)
REFERENCES
sales_persons
(
id
)
ON
DELETE
CASCADE
ON
UPDATE
CASCADE
)
;
Run the following query to insert four records into the sales table.
INSERT
INTO
`sales`
(
`id`
,
`sales_date`
,
`amount`
,
`sp_id`
)
VALUES
(
’90’
,
‘2021-11-09’
,
‘800000’
,
‘1’
)
,
(
’34’
,
‘2020-12-15’
,
‘5634555’
,
‘3’
)
,
(
’67’
,
‘2021-12-23’
,
‘900000’
,
‘1’
)
,
(
’56’
,
‘2020-12-31’
,
‘6700000’
,
‘1’
)
;
Example 1: Use of Group By Clause With a Single Column
Run the following SELECT query to find out the id and name of the salespersons who have records in the sales table. The salesperson id is used for grouping in the Group By clause. According to the content of the sales table, the sales table contains the records of the two salespersons that will be printed in the output:
SELECT
sp_id
AS
ID
,
sales_persons
.
name
AS
`Sales Person`
FROM
sales_persons
,
sales
WHERE
sales_persons
.
id
=
sales
.
sp_id
GROUP
BY
sp_id;
Output:
The following output will appear after executing the previous query:
Example 2: Use of Group By Clause With Multiple Columns
The use of Group By clause with two columns has been shown in the following SELECT query. The salesperson names who have an entry in the sales table for the November month will be printed in the output after executing the query. There is only one entry for the November month in the sales table:
SELECT
sp_id
AS
ID
,
sales_persons
.
name
AS
`Sales Person`
FROM
sales_persons
,
sales
WHERE
sales_persons
.
id
=
sales
.
sp_id
AND
MONTHNAME
(
sales_date
)
=
‘November’
GROUP
BY
sp_id
,
MONTHNAME
(
sales_date
)
;
Output:
The following output will appear after executing the previous query:
Example 3: Use of Group By Clause With the COUNT(*) Function
The uses of the COUNT(*) function with the Group By clause has been shown in the following query. The total number of sales will be counted of each salesperson will be printed after execut-ing the query:
SELECT
sales_persons
.
name
AS
`Sales Person`
,
COUNT
(
*
)
FROM
sales_persons
,
sales
WHERE
sales_persons
.
id
=
sales
.
sp_id
GROUP
BY
sp_id;
Output:
According to the data of the sales table, the following output will appear after executing the pre-vious query:
Example 4: Use of Group By Clause With the COUNT(expression) Function
The uses of the COUNT(expression) function with the Group By clause has been shown in the following query. The total number of sales based on the month name will be counted after exe-cuting the query:
SELECT
MONTHNAME
(
sales
.
sales_date
)
AS
`Month`
,
COUNT
(
MONTH
(
sales
.
sales_date
)
)
AS
`Number of Sales`
FROM
sales
GROUP
BY
MONTHNAME
(
sales
.
sales_date
)
;
Output:
According to the data of the sales table, the following output will appear after executing the pre-vious query:
Example 5: Use of Group By Clause With the COUNT(distinct expres-sion)
The COUNT (expression) function with the Group By clause has been used in the following que-ry to count the total number of sales based on the month name and salesperson id:
SELECT
sp_id
AS
`Sales Person ID`
,
MONTHNAME
(
sales_date
)
AS
MONTH
,
COUNT
(
sp_id
)
AS
`Total sales`
FROM
sales
GROUP
BY
MONTHNAME
(
sales_date
)
,
sp_id;
Output:
According to the data of the sales table, the following output will appear after executing the pre-vious query:
The COUNT(distinct expression) is used in the following query to determine the unique sales based on the month name and the sale person id:
SELECT
sp_id
AS
`Sales Person ID`
,
MONTHNAME
(
sales_date
)
AS
MONTH
,
COUNT
(
DISTINCT
sp_id
)
AS
`Unique Sales Found`
FROM
sales
GROUP
BY
MONTHNAME
(
sales_date
)
,
sp_id;
Output:
According to the data of the sales table, the following output will appear after executing the pre-vious query:
Conclusion:
The simple uses of the Group By clause and the Group By clause with the COUNT() function shown in this tutorial use multiple SELECT queries. The purpose of using Group By clause will be clear after reading this tutorial. We hope you found this article helpful. Check out other Linux Hint articles for more tips and tutorials.
