需要通过错误解析组(ORA-00979:不是按表达式分组)

Question

以下是我的疑问：

  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

在运行上面的SQL时，我得到了如下所述的错误：

ORA-00979:不是按表达式分组 00979。00000 -“非一组表达” *原因：*行动： 行处的错误:5列: 13

我需要帮助来克服上面的错误。

Answer 1

必须将SELECT中的所有列按节放在group函数中。因此，将是：

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

但在那之后，你可能不会得到预期的结果。在这种情况下，您将不得不重写查询。