请教关于sql单表查询（group by）

数据源 表名：tab1

select a.用户，a.任务总数，
(select count(*) from tab1 where 用户=a.用户 and 状态='进行' ) 进行中的数量,
(select count(*) from tab1 where 用户=a.用户 and 状态='延期' ) 延期的数量,
(select count(*) from tab1 where 用户=a.用户 and 状态='取消' ) 取消的数量,
(select count(*) from tab1 where 用户=a.用户 and 状态='完成' ) 完成的数量
from (select 用户,count(*) 任务总数 from tab1  group by  用户) a 

select t2.username,t2.tcount,t3.count1,t3.count2,t3.count3,t3.count4 from
(select username, count(distinct(task)) as tcount from test2 t1 group by username) t2
,
(select username,
COUNT(case states when '进行' then 1 end) count1,
COUNT(case states when '延期' then 1 end ) count2,
COUNT(case states when '取消' then 1 end ) count3,
COUNT(case states when '完成' then 1 end ) count4
from test2 group by username) t3
where t2.username = t3.username;