1

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

Mark Hurd
  • 10,665
  • 10
  • 68
  • 101
user490735
  • 755
  • 2
  • 9
  • 18

2 Answers2

2

As I can guess

First of all GROUP BY operation is performed and then DISTINCT. In GROUP BY clause you must indicate all non-aggregates . For example you are not permitted to do the following:

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
GROUP BY HIRE_DATE

You should do it by this way:

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
GROUP BY HIRE_DATE,FIRST_NAME, LAST_NAME
kupa
  • 1,861
  • 5
  • 25
  • 41
1

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.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • You probably shouldn't remove the distinct. In any case, removing "distinct" will can the output. If two rows have the same first and last name but differant order# they be repeated if the distinct is removed. – redcayuga Apr 08 '11 at 16:16