Oracle Error with Group by

Question

I'm currently trying to use a case statement before a group by command. Getting the below error

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

select "CUSIP", "SIDE", TRADEDATE, ACCOUNTNO, SUM("QUANTITY") as CompressedQuantity,

(case when quantity = 0 THEN 0 ELSE trunc(sum("PRICE"*"QUANTITY")/sum("QUANTITY"),6) END) as AVERAGEPRICE
from rawtable
group by "SIDE", "TRADEDATE", "ACCOUNTNO", "CUSIP")

select CompressedQuantity,"SIDE", "TRADEDATE", "ACCOUNTNO", "CUSIP" FROM compresstable
;

score 1 · Accepted Answer · answered Jul 18 '17 at 17:08

1

You need to check the aggregate sum(quantity), not quantity

select "CUSIP", "SIDE", TRADEDATE, ACCOUNTNO, SUM("QUANTITY") as CompressedQuantity,
    case when sum(quantity) = 0 THEN 0 ELSE trunc(sum("PRICE"*"QUANTITY")/sum("QUANTITY"),6) END as AVERAGEPRICE
from rawtable
group by "SIDE", "TRADEDATE", "ACCOUNTNO", "CUSIP"

answered Jul 18 '17 at 17:08

Serg

22,285
5
21
48

Thanks. This did the trick. – user1060187 Jul 18 '17 at 17:15

score 0 · Answer 2 · edited Jul 18 '17 at 17:19

0

You must add Quantity to your group by.

group by "SIDE", "TRADEDATE", "ACCOUNTNO", "CUSIP", SUM("QUANTITY")

edited Jul 18 '17 at 17:19

t.m.adam

15,106
3
32
52

answered Jul 18 '17 at 17:04

N.Mitts

1

Oracle Error with Group by

2 Answers2