select r.name region,
       prtd.name "Канал продаж",
       si.siebel_user_name "si.siebel_user_name",
       empl.lfm,
       count (case when dg.id=24 and (lower(ct.name) like 'mega%' or lower(ct.name) like 'super%') then ct.name end) ADSL,
      count (case when dg.id=24 and lower(ct.name) like '% net %' or lower(ct.name) like '%idnet %' then ct.name end) FTTH,
       count (case when ct.name like '%FMS%' then ct.name end) fms,
       count (case when lower(ct.name) like '% lte' or lower(ct.name) like '% lte %' then ct.name end) lte

  from db.order_device             od,
       db.abonent                  a,
       db.abonent_type             at,
       db.region                   r,
       db.town                     t,
       db.device_group             dg,
       db.priority                 pr,
       db.report_date              rd,
       db.action                   ac,
       zrdt.employee@zrdt          empl,
       zrdt.portal_department@zrdt prtd,
       db.order_siebel_info        si,
       db.connect_type             ct
 where si.order_id = od.id
   and ct.id = od.new_connect_type_id
   and si.siebel_user_name = empl.siebel_user_name(+)
   and empl.portal_department_id = prtd.id(+)
   and at.id = a.abonent_type_id
   and a.id = od.abonent_id
   and r.id = t.region_id
   and t.id = a.town_id
   and dg.id = od.device_group_id
   and ac.id = od.action_id
       and dg.id in (24 , 64 , 200000,
                    1, 100001, 1000003)
      -- and (od.action_id=5 or od.action_id=6)
   and od.action_id = 5
   and at.id = 1
   and pr.id = od.priority_id
   and pr.id !=2
   and rd.id = 9
   and od.end_sysdate between rd.from_date and rd.to_date
--and od.end_sysdate between to_date('15.06.2018', 'dd.mm.rrrr') and to_date('18.06.2018', 'dd.mm.rrrr')
     -- and lower(prtd.name) like '%цап'
      -- and (lower(prtd.name) like '%црп' or lower(si.siebel_user_name) like 'mendybek%')
      
   group by  si.siebel_user_name , r.name, prtd.name, empl.lfm
 -- group by dg.id