Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MySQL GROUP BY statement

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

MySQL GROUP BY statement

The GROUP BY statement groups the result set according to one or more columns.

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

GROUP BY syntax SELECT column_name, function (column_name) FROM table_nameWHERE column_name operator valueGROUP BY column_name; example demonstration

The example of this chapter uses the following table structure and data. We can import the following data into the database before using it.

SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0Mutual-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 'login', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 -Records of `employee_ tbl`-BEGIN INSERT INTO `employee_ tbl` VALUES ('1Qing,' Xiao Ming', '2016-04-22 15 purse 25 VALUES 33mm,' 1'), ('2mm,' Xiao Wang', '2016-04-20 15 purge 25 purge 47A,' 3'), ('3A', 'Xiao Li', '2016-04-19 1526 purge 02mm,' 2'), ('44th,' Xiao Wang', '2016-04-07 1526 2614') ('4'), ('5', 'Xiao Ming', '2016-04-11 15-15-26-40-40,' 4'), ('6-4, 'Xiao-Ming,' 2016-04-04-15-26-24-54) COMMIT;SET FOREIGN_KEY_CHECKS = 1

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

Mysql > set names utf8;mysql > SELECT * FROM employee_tbl +-+ | id | name | date | singin | +-+ | 1 | Xiaoming | 2016-04-22 15:25 : 33 | 1 | 1 | 2 | Wang | 2016-04-20 15:25:47 | 3 | 3 | Xiao Li | 2016-04-19 15:26:02 | 2 | 4 | Xiao Wang | 2016-04-07 15:26:14 | 4 | 5 | Xiaoming | 2016-04-11 15:26:40 | 4 | 6 | Xiaoming | 2016-04-04 15:26:54 | 2 | +-+- +-+-+ 6 rows in set (0.00 sec)

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

Mysql > SELECT name, COUNT (*) FROM employee_tbl GROUP BY name;+-+-+ | name | COUNT (*) | +-+-+ | Xiaoli | 1 | | Xiaoming | 3 | | Wang | 2 | +-+-+ 3 rows in set (0.01sec) use WITH ROLLUP

WITH ROLLUP can realize the same statistics on the basis of grouped statistics (SUM,AVG,COUNT. ).

For example, we group the above data tables by name, and then count the number of logins for each person:

Mysql > SELECT name, SUM (singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP +-+-+ | name | singin_count | +-+-+ | Xiaoli | 2 | | Xiaoming | 7 | Xiao Wang | 7 | NULL | 16 | +-+-+ 4 rows in set (0.00 sec)

Where the record NULL represents the number of logins for everyone.

We can use coalesce to set a name that can replace NUll, coalesce syntax:

Select coalesce (a _

Parameter description: select b if a==null, c if b==null, an if a b c is null, and null if both a b c are null.

In the following example, if the name is empty, we use the total instead:

Mysql > SELECT coalesce (name, 'Total'), SUM (singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP +-- +-+ | coalesce (name 'Total') | singin_count | +-+-+ | Xiao Li | 2 | | Xiao Ming | 7 | | Xiao Wang | 7 | Total | | 16 | +-- +-+ 4 rows in set (0.01 sec) |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report