How to use group by in mysql

How to use group by in mysql: [SELECT * FROM table_name WHERE column_name GROUP BY column_name;]. The group by statement groups the result set based on one or more columns.

The GROUP BY statement groups the result set based on one or more columns.

We can use COUNT, SUM, AVG, and other functions on grouped columns.

(Recommended tutorial: mysql video tutorial)

Grammar format:

SELECT column_name, function(column_name)FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Example:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ------------------------------  
Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  -- ------------------------------  
  Records of `employee_tbl`
  -- ----------------------------
  BEGIN;INSERT INTO `employee_tbl` VALUES 
  ('1', '小明', '2016-04-22 15:25:33', '1'), 
  ('2', '小王', '2016-04-20 15:25:47', '3'), 
  ('3', '小丽', '2016-04-19 15:26:02', '2'), 
  ('4', '小王', '2016-04-07 15:26:14', '4'), 
  ('5', '小明', '2016-04-11 15:26:40', '4'), 
  ('6', '小明', '2016-04-04 15:26:54', '2');
  COMMIT;SET FOREIGN_KEY_CHECKS = 1;

After the import is successful, execute The following SQL statement:

Next we use the GROUP BY statement to group the data table by name and count how many records each person has:

Related recommendations: mysql tutorial

The above is the detailed content of How to use group by in mysql. For more information, please follow other related articles on the PHP Chinese website!