Group by expression with case statement in oracle?

I have a query with case statement in it.

select
   COUNTRY_CODE,
   CUST_USR_ID,
   (
      case
         when
            FACILITY_ID is not null 
         then
            FACILITY_ID 
         when
            ACCOUNT_NO is not null 
         then
            ACCOUNT_CLASS 
      end
   )
   ACC_FA_ID, count(1), 
   (
      case
         when
            FACILITY_ID is not null 
         then
            'FACILITY_ID' 
         else
            'ACCOUNT_CLASS' 
      end
   )
   IDENTIFIERS 
from
   Mytable 
where
   (
      FACILITY_ID is not null 
      or ACCOUNT_NO is not null
   )
group by
   COUNTRY_CODE, CUST_USR_ID, 
   (
      case
         when
            FACILITY_ID is not null 
         then
            FACILITY_ID 
         when
            ACCOUNT_NO is not null 
         then
            ACCOUNT_CLASS 
      end
   )

This query gives me error

ORA-00979: not a GROUP BY expression

When I remove my last case statement from select then it runs fine. Please help me in this

Answer

Columns that aren’t aggregated should be part of the GROUP BY clause. It means that “solution”

• isn’t to remove the 2nd CASE from SELECT, but to
• include it into GROUP BY

Something like this (CTE is here just to have some sample data in order to show that query works; does it produce what you meant it to, I can’t tell):

SQL> WITH mytable (
  2    country_code, cust_usr_id, facility_id, account_no, account_class
  3  ) AS
  4  (SELECT 1, 1, 1, 1, 1 FROM dual UNION ALL
  5   SELECT 1, 2, 3, 4, 5 FROM DUAL
  6  )
  7  SELECT country_code,
  8         cust_usr_id,
  9         CASE
 10           WHEN facility_id IS NOT NULL THEN
 11             facility_id
 12           WHEN account_no IS NOT NULL THEN
 13             account_class
 14         END acc_fa_id,
 15         COUNT(1),
 16        --
 17         CASE
 18           WHEN facility_id IS NOT NULL THEN
 19             'FACILITY_ID'
 20           ELSE
 21             'ACCOUNT_CLASS'
 22         END identifiers
 23  FROM mytable
 24  WHERE (   facility_id IS NOT NULL
 25         OR account_no  IS NOT NULL)
 26  GROUP BY country_code,
 27           cust_usr_id,
 28           CASE
 29             WHEN facility_id IS NOT NULL THEN
 30               facility_id
 31             WHEN account_no IS NOT NULL THEN
 32               account_class
 33           END,
 34           CASE
 35             WHEN facility_id IS NOT NULL THEN
 36               'FACILITY_ID'
 37             ELSE
 38               'ACCOUNT_CLASS'
 39           END;

COUNTRY_CODE CUST_USR_ID  ACC_FA_ID   COUNT(1) IDENTIFIERS
------------ ----------- ---------- ---------- -------------
           1           2          3          1 FACILITY_ID
           1           1          1          1 FACILITY_ID

SQL>