mysql统计分组求和

mysql统计分组求和

1、由于工作需要对数据进行一个分组展示汇总处理，之前的处理情况如下：
SELECT
a.qxmc as ‘区县’,
count() as ‘下发数’,
SUM(b.sflxs is not null) as ‘核查数’,
SUM(b.sflxs is not null)/count() as ‘完成率’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘查实数’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as ‘查实率’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as ‘管控数’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘管控率’
FROM
t_jcxx a,t_hcryxx b where a.id=b.jcxxid
GROUP BY
a.qxmc

然后通过复制到Excel进行求和汇总处理，由于本人比较懒，所以想找一个直接出汇总的方法，想要的效果图如下：
百度找了很多很多用例，基本没有找到相符合业务场景的sql，后面请教了一个大佬，通过拼接的方式弄出来了：SELECT
a.qxmc as ‘区县’,
count() as ‘下发数’,
SUM(b.sflxs is not null) as ‘核查数’,
SUM(b.sflxs is not null)/count() as ‘完成率’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘查实数’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as ‘查实率’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as ‘管控数’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘管控率’
FROM
t_jcxx a,t_hcryxx b where a.id=b.jcxxid
GROUP BY
a.qxmc
union ALL
select ‘汇总’,sum(a.xfs),sum(a.hcs),sum(a.wcs)/10,sum(a.css),sum(a.csl),sum(a.gks),sum(a.gkl)/10 from (
SELECT
a.qxmc as ‘区县’,
count() as xfs,
SUM(b.sflxs is not null) as hcs,
SUM(b.sflxs is not null)/count() as wcs,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as css,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as csl,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as gks,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as gkl
FROM
t_jcxx a,t_hcryxx b where a.id=b.jcxxid
GROUP BY
a.qxmc
) a
执行结果如下：

但是这个sql太冗余了，很不喜欢，想对它进行优化，最后找了很久很久，终于让我找到了这个宝藏sql函数：COALESCE。说实话这个函数见都没见过，之前问了挺多人，他们的经验都是分组之后不能直接汇总求和了，最后竟然被这一个小小的函数解决了，果然只要想得到，就一定能找到方法，我们言归正传，优化后的sql为：
SELECT
COALESCE(a.qxmc,‘汇总’) as ‘区县’,
count() as ‘下发数’,
SUM(b.sflxs is not null) as ‘核查数’,
SUM(b.sflxs is not null)/count() as ‘完成率’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘查实数’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1)/SUM(b.sflxs is not null) as ‘查实率’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null) as ‘管控数’,
SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1 and b.gkcs is not null)/SUM(b.sflxs=1 and b.sfchbl=1 and b.szdq=1) as ‘管控率’
FROM
t_jcxx a,t_hcryxx b where a.id=b.jcxxid
GROUP BY
a.qxmc WITH ROLLUP

我们来看一下运行结果：

完美契合，是我想要的结果。由于这么罕见的sql函数大家可能也跟我一样没有见过，所以特地发出来给大家安利一下！！！

觉得有用可以意思一下。