select userid,count(id) num,count(case WHEN type =0 then 0 end),count(case WHEN type=1 then 1 end) from tb
where
is_delete=0
GROUP BY userid
select userid,count(id) num,count(case type WHEN 0 then 0 end),count(case type WHEN 1 then 1 end) from tb
where
is_delete=0
GROUP BY userid