sqloracle-databaseoracle10gora-00979

ORA-00979: not a GROUP BY expression

Why does this work

SELECT DISTINCT FIRSTNAME, LASTNAME
FROM books, CUSTOMERS, orders, orderitems
WHERE STATE IN('FL ', 'GA')
GROUP BY orders.order#, firstname, lastname
HAVING SUM(retail*quantity) > 80

but when firstname, lastname is removed from group by it doesn't?

ORA-00979: not a GROUP BY expression

Solution

  • In that case where firstname, lastname are removed from the group by, you get that error because you're SELECTing a column(s) that aren't in the GROUP BY expression, or aren't part of an aggregation/function (i.e. MIN, MAX, AVG, and others).

    You could also eliminate the DISTINCT as well.