SQL 问题之SUM 和 COUNT 组合使用

问题

在开发的时候，遇到一个相对复杂的逻辑，需要先用CASE...WHEN查询数据，再用SUM求和，首先
把我错误的写法，放在下面，供大家参考：（PS: 羞羞羞...） 

SELECT
	a.deptName,
	b.problemNums,
	b.problemNums AS problemRectNums,
	c.dangerNums,
	c.dangerNums AS dangerDealNums,
	c.dangerRectNums 
FROM
	( SELECT DEPT_NAME deptName FROM statistics_city t GROUP BY t.DEPT_NAME ) a
	LEFT JOIN (
	SELECT
		DEPT_NAME,
		IFNULL( count( 1 ), 0 ) problemNums 
	FROM
		statistics_city t,
		inspect_problem pro 
	WHERE
		pro.REMOVED = 0 
		AND t.DEPT_ID = pro.USER_DEPT_ID 
	GROUP BY
		t.DEPT_NAME 
	) b ON a.deptName = b.DEPT_NAME
	LEFT JOIN (
	SELECT
		DEPT_NAME,
		IFNULL( count( 1 ), 0 ) dangerNums,
		SUM(CASE danger.IS_RECTIFICATION
		WHEN 1 THEN
			**COUNT(1)** ELSE 0
		END) AS dangerRectNums
	FROM
		statistics_city t,
		inspect_danger danger 
	WHERE
		danger.REMOVED = 0 
		AND t.DEPT_ID = danger.USER_DEPT_ID 
	GROUP BY
	t.DEPT_NAME 
	) c ON a.deptName = c.DEPT_NAME

错误提示

解析

Invalid use of group function

错误内容是： 集函数的无效用法，多个函数用法冲突
因为不能对包含聚合或子查询的表达式执行聚合函数，SUM内部不能嵌套COUNT方法，

解决办法

将COUNT(1)更改成1，即可
完成SQL如下：

SELECT
	a.deptName,
	b.problemNums,
	b.problemNums AS problemRectNums,
	c.dangerNums,
	c.dangerNums AS dangerDealNums,
	c.dangerRectNums 
FROM
	( SELECT DEPT_NAME deptName FROM statistics_city t GROUP BY t.DEPT_NAME ) a
	LEFT JOIN (
	SELECT
		DEPT_NAME,
		IFNULL( count( 1 ), 0 ) problemNums 
	FROM
		statistics_city t,
		inspect_problem pro 
	WHERE
		pro.REMOVED = 0 
		AND t.DEPT_ID = pro.USER_DEPT_ID 
	GROUP BY
		t.DEPT_NAME 
	) b ON a.deptName = b.DEPT_NAME
	LEFT JOIN (
	SELECT
		DEPT_NAME,
		IFNULL( count( 1 ), 0 ) dangerNums,
		SUM(CASE danger.IS_RECTIFICATION
		WHEN 1 THEN
			1 ELSE 0
		END) AS dangerRectNums
	FROM
		statistics_city t,
		inspect_danger danger 
	WHERE
		danger.REMOVED = 0 
		AND t.DEPT_ID = danger.USER_DEPT_ID 
	GROUP BY
	t.DEPT_NAME 
	) c ON a.deptName = c.DEPT_NAME

THE END