SQL> ed
Wrote file afiedt.buf

  1  with t1 (agent_id,dat,ID) as (
  2  select 'ivanov',to_date('01.01.2000 0:00','DD.MM.YYYY HH24:MI'),   1 from dual union all
  3  select 'Petrov',to_date('02.01.2000 10:00','DD.MM.YYYY HH24:MI'),  1 from dual union all
  4  select 'Sidorov',to_date('03.01.2000 10:00','DD.MM.YYYY HH24:MI'), 2 from dual union all
  5  select 'ivanov',to_date('01.01.2000 0:00','DD.MM.YYYY HH24:MI'),   1 from dual union all
  6  select 'Petrov',to_date('02.01.2000 10:00','DD.MM.YYYY HH24:MI'),  3 from dual union all
  7  select 'Sidorov',to_date('03.01.2000 10:00','DD.MM.YYYY HH24:MI'), 1 from dual union all
  8  select 'ivanov',to_date('01.01.2000 0:00','DD.MM.YYYY HH24:MI'),   2 from dual union all
  9  select 'Petrov',to_date('02.01.2000 10:00','DD.MM.YYYY HH24:MI'),  3 from dual union all
 10  select 'Sidorov',to_date('03.01.2000 10:00','DD.MM.YYYY HH24:MI'), 1 from dual union all
 11  select 'Petrov',to_date('01.01.2000 0:00','DD.MM.YYYY HH24:MI'),   2 from dual union all
 12  select 'Sidorov',to_date('02.01.2000 10:00','DD.MM.YYYY HH24:MI'), 3 from dual
 13  )
 14  ,t2 (ID_t, name    ) as (
 15  select 1,'snikers' from dual union all
 16  select 2,'mars'            from dual union all
 17  select 3,'bounti'  from dual
 18  )
 19  select
 20    agent_id
 21   ,count(decode(trunc(dat),date '2000-01-01',1)) cc_2000_01_01
 22   ,count(case when trunc(dat)=date '2000-01-01' and name='snikers' then 1 end) cc_snikers
 23  from t1,t2 where t1.id=t2.id_t(+)
 24  group by agent_id
 25* order by 1
SQL> /

AGENT_I CC_2000_01_01 CC_SNIKERS
------- ------------- ----------
ivanov              3          2
Petrov              1          0
Sidorov             0          0