CODEWITHSUNDEEP
mysql
scott
scott

Reputation: 3202

MYSQL group by query

I have following table in my MySQL database

busnames

id |busname

routes

id|route_name

timings table

id | bus_id | route_id | trip |direction |bus_time

Now I want to display data group by trip where bus_id="some_id" and for each page per trip data. If I click next, then I need to show second trip data 

BusRouteName |Trip | Departure | Destination|

Newyork        1         8 a.m       10.am
Ausi           1         9 a.m       11.20 a.m

In the above data bus traveling from Newyork at 8 a.m and return at 10.am in first trip

I have tried many queries in MySQL, but it doesn't seem to produce the correct output

SELECT * FROM timings t inner join routes r on t.route_id=r.id inner join busnames b on t.bus_id=b.id where b.id=1

Can anyone tell me how can I get the data based on trip?

Upvotes: 0

Views: 112

Answers (1)

Fabian Lang
Fabian Lang

Reputation: 78

Maybe try to group by the BusRoute and Trip and use the min and max values of the bus_time to get the departure and destination time.

Something like this (not tested):

SELECT r.route_name, t.trip, min(t.bus_time) as Departure, max(t.bus_time) as Destination FROM timings t 
inner join routes r on t.route_id=r.id 
inner join busnames b on t.bus_id=b.id 
where b.id=1
group by r.route_name, t.trip

If you have more complex conditions you can look at the partition by function. See https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html

Upvotes: 1

Related Questions