GROUP BY error

Hi Experts,

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

Open in new window


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 

Open in new window


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.
APD TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
GROUP BY occurs AFTER the WHERE
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

WHERE (trips.date BETWEEN '2013-07-01' AND '2013-07-15')
AND res.archived = 0
AND res.pickup_status != 'X'

GROUP BY trips.date
       , trips.run
       , trips.capacity
       , trips.vehicle_id
       , res.niagara_trip_id

ORDER BY trips.date ASC, trips.run ASC

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoSoftware DeveloperAuthor Commented:
Thankl you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.