Mysql group by having instance code, mysqlhaving

Mysql group by having instance code, mysqlhaving

Mysql group by having instance

Note:When using group by, the column name in the SELECT clause must be a group column. The following instance must include the name column, because name is a condition for group.

Instance:

My database has an employee work record table. The database in the table is as follows:

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)

Requirement 1: Now I need to find out how many days each employee has worked, and then I need to use the group by statement:

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)

Requirement 2: I want to find all employees who work more than one day now. In this case, the group by having statement is used.

mysql> SELECT name, COUNT(*)  -> FROM  employee_tbl   -> GROUP BY name having COUNT(*)>1 ;+------+----------+| name | COUNT(*) |+------+----------+| Jack |    2 || Zara |    2 |+------+----------+2 rows in set (0.04 sec)

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!