GROUP BY ORACLE SQL. ORA-00979

#oracle #group_by #sql

Уважаемые коллеги, прошу подсказать ЧЯДНТ? Почему 


  ORA-00979: выражение не является выражением GROUP BY?


Запрос: 

select cp.name as bank_name,
       case 
         when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
              and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
         then count(p.guid) 
       end as "2014",
       case
         when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
              and to_date('20.03.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
         then count(p.guid) 
       end as "2015"
from   cp_providers cp
left   join payments p 
on     cp.guid = p.cpp_guid
where  p.payment_date = '01.01.70'
and    p.is_active = 1 
group  by cp.name;


Большое спасибо!
    

Ответы

Ответ 1

Видимо, ошибка в использовании case и функции группировки count.
Как вариант, можно сделать так:

select cp.name as bank_name,
       COUNT(case
               when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                    and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
               then p.guid
             else NULL 
       end) as "2014",
       COUNT(case
               when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                    and to_date('20.03.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
               then p.guid
            else NULL 
       end) as "2015"
from   cp_providers cp
left   join payments p 
on     cp.guid = p.cpp_guid
where  p.payment_date = '01.01.70'
and    p.is_active = 1 
group  by cp.name;


Ответ 2

Попробуйте так:

select bank_name, 
       sum("2014") as "2014", 
       sum("2015") as "2015"
from   (select cp.name as bank_name,
               case 
                 when p.register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                  and to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
                 then 1
                 else 0
               end as "2014",
               case
                 when p.register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy
hh24:mi:ss')
                  and to_date('20.03.2015 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
                 then 1
                 else 0
               end as "2015"
        from   cp_providers cp
        left   join payments p 
        on     cp.guid = p.cpp_guid
        where  p.payment_date = '01.01.70'
        and    p.is_active = 1)
 group  by bank_name;


P.S. У Вас ошибка в дате - '20.03.2014 23.59.59', как я понимаю, там должно быть 2015


Ответ 3

Как вариант:

select cp.name, 
       sum(p.c2014) as "2014", 
       sum(p.c2015) as "2015"
from   cp_providers cp 
join   (
       select cpp_guid, 
              count(*) as c2014, 
              0 as c2015
       from   payments 
       where  payment_date = '01.01.70'
       and    is_active = 1
       and    register_date between to_date('01.01.2014 00.00.00', 'dd.mm.yyyy hh24:mi:ss')
       and    to_date('31.12.2014 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
       group  by cpp_guid    
       union  all
       select cpp_guid, 
              0, 
              count(*)
       from   payments
       where  payment_date = '01.01.70'
       and    is_active = 1
       and    register_date between to_date('01.01.2015 00.00.00', 'dd.mm.yyyy hh24:mi:ss')
       and    to_date('31.12.2015 23.59.59', 'dd.mm.yyyy hh24:mi:ss')
       group  by cpp_giud
       ) p 
on    cp.guid = p.cpp_guid
group by cp.name