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.

Answer 1

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

Answer 2

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
  )

Answer 3

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