I have a database with 2 tables. The first Niagara trips table contains trip id (auto increment), date, run number, vehicle and capacity. The second reservation table has a foreign key to the Niagara trips id, total passengers(pax), name, prices and other irrelevant information to this question. Basically many reservations can be assigned to 1 Niagara trip.
I need to get a summary that will list the date, run number, vehicle id and the total number of passengers assigned to that trip within a given date range. In other words if the date range spans 3 days, and there are 4 runs in each of those days, I expect 12 records. With that said, I came up with the following query:
SELECT trips.date, trips.run, trips.capacity, trips.vehicle_id, SUM(res.total_pax) AS total_trip_pax
FROM niagara_trips AS trips INNER JOIN reservations AS res
ON trips.id = res.niagara_trip_id
GROUP BY trips.date, trips.run, trips.capacity, trips.vehicle_id, res.niagara_trip_id
WHERE (trips.date BETWEEN '2013-07-01' AND '2013-07-15')
AND res.archived = 0 AND res.pickup_status != 'X'
ORDER BY trips.date ASC, trips.run ASC
However, I keep getting the following error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (trips.date BETWEEN '2013-07-01' AND '2013-07-15')
AND res.archived = 0 A' at line 8
If I remove line 6 from my query the error goes away, but I can only 1 record instead of the 12.
Any help will be appreciated.