Article directory
MySQL Sum group by usage?MySQL databaseDetailed explanation of group by count
MySQL GROUP BY statement
The GROUP BY statement groups the result set based on one or more columns.
On the grouped column we can use COUNT, SUM, AVG, etc. functions.
GROUP BY syntax
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
Example demonstration
The example in this chapter uses the following table structure and data. Before using it, we can import the following data into the database.
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:
mysql> set names utf8; mysql> SELECT * FROM employee_tbl; +----+--------+---------------------+--------+ | id | name | date | singin | +----+--------+---------------------+--------+ | 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 | +----+--------+---------------------+--------+ 6 rows in set (0.00 sec)
Next we use the GROUP BY statement to group the data table by name and count how many records each person has:
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*) | +--------+----------+ | 小丽 | 1 | | 小明 | 3 | | 小王 | 2 | +--------+----------+ 3 rows in set (0.01 sec)
Use WITH ROLLUP
WITH ROLLUP can implement the same statistics (SUM, AVG, COUNT...) on the basis of grouped statistics.
For example, we group the above data table by name, and then count the number of times each person has logged in:
mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------+--------------+ | name | singin_count | +--------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | NULL | 16 | +--------+--------------+ 4 rows in set (0.00 sec)
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,b,c);
Parameter description: if a==null, choose b; if b==null, choose c; if a!=null, choose a; if abc is null, return null (meaningless).
In the following example if the name is empty we use the total instead:
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------------------------+--------------+ | coalesce(name, '总数') | singin_count | +--------------------------+--------------+ | 小丽 | 2 | | 小明 | 7 | | 小王 | 7 | | 总数 | 16 | +--------------------------+--------------+ 4 rows in set (0.01 sec)
Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) shared "MySQL sum group by usage? Detailed Explanation of Group by Count in MySQL Database" is helpful to you.
Welcome to share the link of this article:https://www.chenweiliang.com/cwl-477.html
Welcome to the Telegram channel of Chen Weiliang's blog to get the latest updates!
📚 This guide contains huge value, 🌟This is a rare opportunity, don’t miss it! ⏰⌛💨
Share and like if you like!
Your sharing and likes are our continuous motivation!