Running into issues with SQL Error 1111

Question

I am creating an instance in which the customer has more than 1 reservation. To do this, each time the customer number is listed more than once in the reservation table, this signifies that they have more than one reservation (which again, is the condition). Unfortunately, when I attempt to run this query I get:

Error Code: 1111 (Invalid use of group function).

Here is what I have done below.

SELECT FirstName, LastName, tripName
FROM reservation, customer, trip
WHERE reservation.CustomerNum = customer.CustomerNum
AND reservation.TripID = trip.TripID
AND COUNT(reservation.CustomerNum) > 1
GROUP BY reservation.CustomerNum;

I am very new to SQL, any advice will be very helpful.

Check the answer here: http://stackoverflow.com/questions/22141968/error-code-1111-invalid-use-of-group-function. Basically you need to move your count to a having clause. — Erik Nedwidek, Mar 24 '17 at 16:55

score 0 · Answer 1 · edited Mar 24 '17 at 16:59

0

If you are using GROUP BY, all the fields you select must be in an aggregate function or included in the GROUP BY clause.

edited Mar 24 '17 at 16:59

Justin Burgard

440
1
6
17

answered Mar 24 '17 at 16:56

z m

1,493
2
19
21

score 0 · Answer 2 · answered Mar 24 '17 at 16:56

You need to write proper joins, using aliases helps keep things readable and saves you extra keystrokes, and you would need to use something like this to limit your results to those with more than one reservation:

select FirstName, LastName, tripName
from customer c 
  inner join reservation r
    on c.CustomerNum = r.CustomerNum
  inner join trip t
    on r.TripID = t.TripID
where c.CustomerNum in (
  select ir.CustomerNum
  from reservation ir
  group by ir.CustomerNum
  having count(*) > 1
  )

Thank you. I was unaware of the 'HAVING' keyword. Very helpful, I will start getting in the habit of using the proper joins and aliases. — Alex Kennedy, Mar 24 '17 at 17:49

score 0 · Answer 3 · answered Mar 24 '17 at 16:59

You must use having for filter an aggregated result (not where)

  SELECT FirstName, LastName, tripName
  FROM reservation
  INNER JOIN customer on reservation.CustomerNum = customer.CustomerNum
  INNER JOIN trip on reservation.TripID = trip.TripID
  GROUP BY reservation.CustomerNum;
  having  COUNT(reservation.CustomerNum) > 1

Running into issues with SQL Error 1111

3 Answers3