Need to resolve the group by error (ORA-00979: not a GROUP BY expression)

Question

Here is my query:

  select 
    A.school_dim_id,
    A.platform_dim_id,
    A.month_dim_id,
    CASE WHEN B.SCHOOLTYPE NOT IN ('International') THEN B.ROLLSIZE
    WHEN B.SCHOOLTYPE IN ('International') THEN MAX(D.ROLE_SIZE)
    END ROLL_SIZE
  from  
      rag_raw_event_detail_fact A inner join rag_school_dim B on (A.school_dim_id = B.school_dim_id)
      inner join rag_subscription_detail_fact C on (A.school_dim_id = C.school_dim_id)
      inner join rag_intl_school_roll_static D on (D.isbn = c.isbn)
      inner join rag_platform_dim E on (E.platform_dim_id = A.platform_dim_id)
  where 
      E.PLATFORM_EVENT = 'alp_wordsmith_resources_opened'
      and C.service in ('PriHubsWordsmith','PriHubsWordsmithGlobal')
      and C.enddate > sysdate
  group by
      A.school_dim_id,
      A.platform_dim_id,
      A.month_dim_id

While running the above SQL, I'm getting the error as mentioned below:

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 5 Column: 13

I need help to overcome the above error.

You have columns names `ROLLSIZE` and `ROLL_SIZE` in your data? You are mixing aggregation functions and non-aggregation in the same `case`. I think you should provide sample data and desired results. You query has problems. — Gordon Linoff, Oct 12 '15 at 12:07
Hi Gordon, i have rag_school_dim.ROLLSIZE and rag_intl_school_roll_static.ROLE_SIZE as column name in two different tables. if the rag_school_dim.schooltype is not 'International' have to take the rollsize from school_dim table, else max(role_size) from rag_intl_school_roll_static — Raj, Oct 12 '15 at 12:21

score 0 · Answer 1 · answered Oct 12 '15 at 13:06

You must place all columns from SELECT which is not in group function in GROUP BY section. So it will be:

select 
    A.school_dim_id,
    A.platform_dim_id,
    A.month_dim_id,
    CASE WHEN B.SCHOOLTYPE NOT IN ('International') THEN B.ROLLSIZE
    WHEN B.SCHOOLTYPE IN ('International') THEN MAX(D.ROLE_SIZE)
    END ROLL_SIZE
  from  
      rag_raw_event_detail_fact A inner join rag_school_dim B on (A.school_dim_id = B.school_dim_id)
      inner join rag_subscription_detail_fact C on (A.school_dim_id = C.school_dim_id)
      inner join rag_intl_school_roll_static D on (D.isbn = c.isbn)
      inner join rag_platform_dim E on (E.platform_dim_id = A.platform_dim_id)
  where 
      E.PLATFORM_EVENT = 'alp_wordsmith_resources_opened'
      and C.service in ('PriHubsWordsmith','PriHubsWordsmithGlobal')
      and C.enddate > sysdate
  group by
      A.school_dim_id,
      A.platform_dim_id,
      A.month_dim_id,
      B.SCHOOLTYPE,
      B.ROLLSIZE

But after that you may get not expected result. In that case you will have to rewrite your query.

you can mark answer as correct if it solves your problem ;) – Tatiana Oct 12 '15 at 16:27 — Tatiana, Oct 12 '15 at 16:27

Need to resolve the group by error (ORA-00979: not a GROUP BY expression)

1 Answers1