异常的邮件概率 分组条件计数count(case when)

/*with not_blacklist as(
    select * from email
    where send_id not in (select id from user where is_blacklist=1)
    and receive_id not in (select id from user where is_blacklist=1) 
),
with countby_date as(
    select date,
    count(case when type='no_completed' then 1 else null end) no_com_cnt,
    count(id) date_cnt
from not_blacklist
group by date
)  

select date,
    round(no_com_cnt/date_cnt,3) p
from countby_date
order by date
先创建countby_date临时表，再计算概率，就一直报错，为什么呢？？？？
*/

with not_blacklist as(
    select * from email
    where send_id not in (select id from user where is_blacklist=1)
    and receive_id not in (select id from user where is_blacklist=1) 
)

select date,
    round(count(if(type='no_completed',1,null))/count(id),3) p
	/*或者 round(count(case when type='no_completed' then 1 else null end)/count(id),3) p */
from not_blacklist
group by date
order by date

分组条件计数：

select

count(case when condition then ... else ... end)

或

count(if(condition,value1,value2))

from table

group by column