一种选择是使用条件聚合。
样本数据：

SQL> with test (myid, status, owner, datum) as
  2  (select
  3  'a001', 'open', 'John', '0901' from dual union all select
  4  'a001', 'cncl', 'John', '0901' from dual union all select
  5  'a002', 'open', 'John', '0901' from dual union all select
  6  'a002', 'open', 'Andy', '0901' from dual union all select
  7  'a003', 'open', 'John', '0901' from dual union all select
  8  'a003', 'open', 'John', '0902' from dual union all select
  9  'a003', 'open', 'John', '0902' from dual
 10  )

查询方式：

11  select myid,
 12    count(*),
 13    sum(case when status = 'cncl' then 1 else 0 end) cncl,
 14    sum(case when owner  = 'John' then 1 else 0 end) john,
 15    sum(case when datum  = '0901' then 1 else 0 end) datum
 16  from test
 17  group by myid
 18  order by myid;

MYID   COUNT(*)       CNCL       JOHN      DATUM
---- ---------- ---------- ---------- ----------
a001          2          1          2          2
a002          2          0          1          2
a003          3          0          3          1

SQL>