This article briefly introduces the use of the GROUPBY clause in MySQL, which is the basic knowledge in MySQL beginners. if you need a group by clause, you can refer to the group by clause column, if you want to, you can calculate the column. Use the COUNT, SUM, AVG, and other functions to group columns.
The tables of the group by clause that consider the EMPLOYEE_TBL have the following records:
mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)
Now, based on the table above, we want to calculate the number of working days for each employee.
If we compile an SQL query, as shown below, we will get the following results:
mysql> SELECT COUNT(*) FROM employee_tbl;+---------------------------+| COUNT(*) |+---------------------------+| 7 |+---------------------------+
However, this is not our target service. we want to display the total number of separate pages for each user. This is done BY using the aggregate function together with the group by clause as follows:
mysql> SELECT name, COUNT(*) -> FROM employee_tbl -> GROUP BY name;+------+----------+| name | COUNT(*) |+------+----------+| Jack | 2 || Jill | 1 || John | 1 || Ram | 1 || Zara | 2 |+------+----------+5 rows in set (0.04 sec)