Group By Clause:
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:
Run the following command to create a database named test_db:
Run the following command to select the database:
Run the following query to create a table named sales_persons with four fields:
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
contact_no VARCHAR(30));
Run the following query to insert the three records into the sales_person table:
(NULL, 'Nila Hossain', '[email protected]', '01855342357'),
(NULL, 'Abir Hossain', '[email protected]', '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.
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.
('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:
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:
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:
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:
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:
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:
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.
