Oracle: Sum function “not a single-group group function error”

oracleoracle-11g

I have an ORDER_LINE table in which I am trying to multiply the NUM_ORDERED of parts by the QUOTED_PRICE for each ORDER_NUM. I am adding SUM() function to group by ORDER_NUM, because if I didn't, there would be multiple entries for the same order.

ORDER_LINE Table:

ORDER_NUM   PART_NUM    NUM_ORDERED     QUOTED_PRICE
21608       AT94        11              21.95
21610       DR93        1               495
21610       DW11        1               399.99
21613       KL62        4               329.95
21614       KT03        2               595
21617       BV06        2               794.95
21617       CD52        4               150
21619       DR93        1               495
21623       KV29        2               1290

Query:

SELECT ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
ORDER BY ORDER_NUM;

Expected Result:

ORDER_NUM   TOTAL_AMOUNT
21608       241.45
21610       894.99
21613       1319.8
21614       1190
21617       2189.9
21619       495
21623       2580

Error ORA-00937: not a single-group group function

If I remove the Sum function I get the following result:

SELECT ORDER_NUM, (NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
ORDER BY ORDER_NUM;

ORDER_NUM   TOTAL_AMOUNT
21608       241.45
21610       495
21610       399.99
21613       1319.8
21614       1190
21617       1589.9
21617       600
21619       495
21623       2580

Best Answer

Unless I'm missing something, you just need to add GROUP BY ORDER_NUM, don't you? 

SELECT ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT
FROM
ORDER_LINE
GROUP BY ORDER_NUM
ORDER BY ORDER_NUM;
Related Question