Not a GROUP BY expression (Oracle)

Question

I want to aggregate my query by a certain field but I keep getting the error message as stated in the title.

My code is:

select cluid, step, count(*) as CNT--, json_value(e.event_payload, '$.type') as type
from VDS_OWNER.PROTO_BEAN_COUNTER_EVENTS_BASE2 e
where json_value(e.event_payload, '$.type') = 'page'
    and e.STEP <> 'overview'
    and ROWNUM <= 10000
group by e.CLUID

What am I doing wrong?

score 1 · Accepted Answer · answered Dec 14 '20 at 13:51

All unaggregated columns in the select should be in the GROUP BY:

select cluid, step, count(*) as CNT--, json_value(e.event_payload, '$.type') as type
from VDS_OWNER.PROTO_BEAN_COUNTER_EVENTS_BASE2 e
where json_value(e.event_payload, '$.type') = 'page' and
      e.STEP <> 'overview' and
      ROWNUM <= 10000
group by e.CLUID, e.step;

If you only want one row per CLUID, then remove step from the select:

select cluid, count(*) as CNT--, json_value(e.event_payload, '$.type') as type
from VDS_OWNER.PROTO_BEAN_COUNTER_EVENTS_BASE2 e
where json_value(e.event_payload, '$.type') = 'page' and
      e.STEP <> 'overview' and
      ROWNUM <= 10000
group by e.CLUID;

Or use listagg() or some other appropriate aggregation function if you want information about steps but only one row per CLUID.

Not a GROUP BY expression (Oracle)

1 Answers1