使用count case when 返回0还是null的问题

记录一条遇到的sql执行的坑

执行sql如下:

SELECT
	d.`insurance_account_id` AS insuranceAccountId,
	max( d.`insurance_account_name` ) AS insuranceAccountName,
	max( d.pay_city_name ) AS payCityName,
	max( d.declare_type ) AS declareType,
	COUNT( CASE WHEN d.operate_type = 1 THEN 1 ELSE NULL END ) AS addCount,
	COUNT( CASE WHEN d.operate_type = 2 THEN 1 ELSE NULL END ) AS delCount 
FROM
	`social_fund_declare` d
	INNER JOIN social_fund_push_status ps ON d.id = ps.social_fund_declare_id 
WHERE
	d.`declare_status` NOT IN ( 1, 5 ) 
	AND d.is_del = 0 
	AND ps.insured_result = 0 
	AND ps.is_delete = 0 
	AND ps.push_status = 3 
GROUP BY
	d.insurance_account_id;

返回结果为:

 结果显示没有记录,所有的值都为null,没有任何一条记录

但是如果我把上面sql中分组的 GROUP BY d.insurance_account_id 去掉

SELECT
	d.`insurance_account_id` AS insuranceAccountId,
	max( d.`insurance_account_name` ) AS insuranceAccountName,
	max( d.pay_city_name ) AS payCityName,
	max( d.declare_type ) AS declareType,
	COUNT( CASE WHEN d.operate_type = 1 THEN 1 ELSE NULL END ) AS addCount,
	COUNT( CASE WHEN d.operate_type = 2 THEN 1 ELSE NULL END ) AS delCount 
FROM
	`social_fund_declare` d
	INNER JOIN social_fund_push_status ps ON d.id = ps.social_fund_declare_id 
WHERE
	d.`declare_status` NOT IN ( 1, 5 ) 
	AND d.is_del = 0 
	AND ps.insured_result = 0 ​ 
	AND ps.is_delete = 0 
	AND ps.push_status = 3;

查询结果如下:

 这样就产生了一条记录, 而且addCount和delCount的值为0, 

使用聚合函数时一定要使用GROUP BY进行分组, 否则会多查出一条记录