玖叶教程网

分组统计在日常工作中经常用到，今天分享使用sum,if进行分组统计。

IF表达式：

IF(expr1,expr2,expr3)

如果 expr1 是TRUE，则返回值是expr2; 否则返回值是expr3。

查询每个部门男生、女生分别多少人？

select
dept_id,
count(*) as total_num,
sum(if(sex=1,1,0)) as female_num,
sum(if(sex=0,1,0)) as male_num,
sum(if((sex<>1 and sex<>0) or sex is null,1,0)) as unknown_num
from staff 
group by dept_id;

+---------+-----------+------------+----------+-------------+
| dept_id | total_num | female_num | male_num | unknown_num |
+---------+-----------+------------+----------+-------------+
| 101     |         5 |          2 |        2 |           1 |
| 102     |         3 |          1 |        1 |           1 |
| 103     |         1 |          1 |        0 |           0 |
| 104     |         1 |          0 |        1 |           0 |
+---------+-----------+------------+----------+-------------+
4 rows in set (0.00 sec)

表结构如下：

CREATE TABLE `staff` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `dept_id` varchar(10) DEFAULT NULL COMMENT '部门id',
  `salary` double DEFAULT NULL COMMENT '工资',
  `sex` int(11) DEFAULT NULL COMMENT '性别，1是男生，0是女生',
  `created_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='职员表'

现有记录：

mysql>  select * from staff ;
+----+-------+---------+--------+------+---------------------+---------------------+
| id | name  | dept_id | salary | sex  | created_time        | updated_time        |
+----+-------+---------+--------+------+---------------------+---------------------+
|  1 | Tom   | 101     |   2500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  2 | Tonny | 101     |   3500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  3 | Bob   | 101     |   3500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  4 | Rob   | 101     |   4500 | NULL | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  5 | Sudey | 101     |   5500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  6 | Sunny | 102     |   5500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  7 | Sedey | 102     |   3500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  8 | Bobby | 102     |   4500 | NULL | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
|  9 | Weedy | 103     |   4500 |    1 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
| 10 | Jimme | 104     |   5500 |    0 | 2020-03-08 18:37:55 | 2020-03-08 18:37:55 |
+----+-------+---------+--------+------+---------------------+---------------------+
10 rows in set (0.00 sec)

当然也可以使用case when实现，详见：

MySQL分组统计你会吗？case when,sum,count会用吗？

MySQL,case when你真的会用吗？附避坑指南

「欢迎关注，每天更新工作实用技能」