GROUP BY ORACLE SQL. ORA-00979

Dear colleagues, I ask you to suggest CHADNT? Why

ORA-00979: the expression is not a GROUP BY expression?

Request: 

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;

Thank you very much!

Author: Viktorov, 2015-03-20
Source

3 answers

Apparently, there is an error in using case and the count grouping function. Alternatively, you can do this:

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;
 4
Author: evz, 2017-11-20 09:12:04

Try this way:

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. You have an error in the date - '20.03.2014 23.59.59', as I understand it, there should be 2015

 2
Author: Valeriy Karchov, 2017-11-20 09:16:17

As an option:

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
 1
Author: Yura Ivanov, 2017-11-20 09:20:12