Why am I getting Error in Group BY statement while using hardcoded values in WHERE clause?

Question

I am new to PostgreSQL. I am working on a taxi ride booking application.

I am trying to find why the (1) doesn't work. The hardcoded values are to be replaced by user inputs.

1. Non Working:

   SELECT MAX(cr.pickup_point) AS pickup_point , MAX(cr.destination) AS destination, MAX(cr.leave_time) AS leave_time,MAX(cr.license) AS license , MAX(cr.username) AS username, MAX(cr.car_ride_id) AS car_ride_id , MAX(isd.no_of_seats) AS no_of_seats FROM car_ride cr , is_driver isd WHERE cr.username = isd.username AND cr.license = isd.license AND LOWER(TRIM(pickup_point))::varchar = LOWER(TRIM('Paya Lebar East, Paya Lebar'))::varchar AND 
 (LOWER(TRIM(cr.destination))::varchar = LOWER(TRIM('Kranji, Sungei Kadut'))::varchar AND (cr.leave_time)> now()::timestamp(0) GROUP BY(cr.car_ride_id, isd.no_of_seats) HAVING isd.no_of_seats> ALL (SELECT COUNT(*) FROM bids b , car_ride cr2 WHERE b.driver_username = cr2.username AND b.successful IS TRUE AND b.leave_time >now()::timestamp(0) AND cr2.leave_time = b.leave_time AND cr.car_ride_id = cr2.car_ride_id)

2.Working:

SELECT  MAX(cr.pickup_point) AS pickup_point , MAX(cr.destination)
AS destination , MAX(cr.leave_time) AS leave_time ,
MAX(cr.license) AS license , MAX(cr.username)
AS username, MAX(cr.car_ride_id) AS car_ride_id , MAX(isd.no_of_seats) AS no_of_seats
FROM car_ride  cr , is_driver isd WHERE cr.username = isd.username
AND cr.license =  isd.license AND
(cr.leave_time)> now()::timestamp(0) GROUP BY(cr.car_ride_id, isd.no_of_seats)  HAVING
isd.no_of_seats>
ALL (SELECT COUNT(*) FROM bids b , car_ride cr2 WHERE
b.driver_username = cr2.username AND b.successful IS TRUE
AND  b.leave_time >now()::timestamp(0) AND cr2.leave_time = b.leave_time
AND cr.car_ride_id = cr2.car_ride_id )

The difference between (1.)and (2.) is that the former intends to filter the results on the basis of pickup points and destinations.

While (2) displays all the car ride adverts that are in the future and not fully booked (number of bids= TRUE for a particular car_ride_id < car_capacity) for the car to not be fully booked.

Note that I have used the MAX aggregates so many times because I have used them as hidden fields in a subsequent form for making a bid.

Results for 2nd query

Error message upon running the 1st query.

ERROR: syntax error at or near "GROUP"

LINE 10: GROUP BY(cr.car_ride_id, isd.no_of_seats)

Answer 1

You have one unnecessary bracket that you did not close, between AND and LOWER:

  AND (LOWER(TRIM(cr.destination))::varchar = LOWER(TRIM('Kranji, Sungei Kadut'))::varchar

Fixed:

SELECT MAX(cr.pickup_point) AS pickup_point , MAX(cr.destination) AS destination,
       MAX(cr.leave_time) AS leave_time,MAX(cr.license) AS license ,
       MAX(cr.username) AS username, MAX(cr.car_ride_id) AS car_ride_id ,
       MAX(isd.no_of_seats) AS no_of_seats
  FROM car_ride cr , is_driver isd
 WHERE cr.username = isd.username
   AND cr.license = isd.license
   AND LOWER(TRIM(pickup_point))::varchar = LOWER(TRIM('Paya Lebar East, Paya Lebar'))::varchar
   AND /*(*/LOWER(TRIM(cr.destination))::varchar = LOWER(TRIM('Kranji, Sungei Kadut'))::varchar
   AND (cr.leave_time)> now()::timestamp(0)
 GROUP BY(cr.car_ride_id, isd.no_of_seats)
HAVING isd.no_of_seats> ALL (SELECT COUNT(*)
                               FROM bids b , car_ride cr2
                              WHERE b.driver_username = cr2.username
                                AND b.successful IS TRUE
                                AND b.leave_time >now()::timestamp(0)
                                AND cr2.leave_time = b.leave_time
                                AND cr.car_ride_id = cr2.car_ride_id)

It is easier to avoid those mistakes when you format queries in a bit more readable style.