CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloper
Himanshu Tiwari
Himanshu Tiwari

Reputation: 95

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

Upvotes: 0

Views: 169

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 0

Related Questions