Php group by

[amansingh]

Please advise me, to this following group function in PHP. this works perfectly fine,it groups the airline name and starts with the MIN price, however the problem is that it also shows the season but it is picking the MIN season which is not required. I want it to pick the season for the MIN price which it applies for in the same row.

the code is below...all help is much appreciated...thank you in advance:

$sql="SELECT MIN(price),dep,des,airline,flighttype,baggage,season,cabin,id FROM faresheet WHERE cabin = '".$q."' AND des = 'DEL' GROUP BY airline ORDER BY MIN(price) ASC";

[urtrivedi]

I dont know how your query is working, because column list and group by list is not matching in your query.

to find min only by arline

$sql="SELECT MIN(price),airline FROM faresheet WHERE cabin = '".$q."' AND des = 'DEL' GROUP BY airline ORDER BY MIN(price) ASC";

to find min only by arline and season

$sql="SELECT MIN(price),airline,season FROM faresheet WHERE cabin = '".$q."' AND des = 'DEL' GROUP BY airline,season ORDER BY MIN(price) ASC";

[amansingh]

Thank you for the reply, the problem is that MIN price is not a issue, as it shows the record with the MIN price from my table, however each record has its own SEASON, and where my code goes wrong is that it does not show the SEASON which is in the same row as the MIN price, it shows the SEASON which is the MIN SEASON picked randomly from any other record (basically whichever SEASON starts first).

I want it to show the record with the MIN price (which it does fine), but I also want it to show the SEASON which is in the same ROW as the MIN price grouped by the AIRLINE NAME and not the MIN SEASON.

Please help, or any advice

[hag++]

The way your query is written now it assumes that there could be multiple prices for a given Airline / Season combo. So if your data looks like this:

Airline Season Price
------- ------ -----
Airline1 Summer 50
Airline1 Summer 65
Airline1 Summer 40
Airline1 Winter 55
Airline1 Winter 105

Your query would return:

Airline1, Summer, 40
Airline1, Winter, 55

Is that what you need?

[amansingh]

So if your data looks like this:

Airline Season Price
------- ------ -----
Airline1 Summer 50
Airline1 Summer 65
Airline1 Summer 40
Airline2 Winter 55
Airline2 Winterindia 105
Airline3 Winterlove 110
Airline3 Winter 111
Airline4 Winter 105
Airline4 Winteruk 95

query would return:

Airline1, Summer, 40
Airline2, Winter, 55
Airline3, Winterlove, 110
Airline4, Winteruk, 95

MIN(price) group only airline and other field return normal , same fr min price row.

[amansingh]

Please ignore first reply

So if your data looks like this:

Airline Season Price
------- ------ -----
Airline1 Summer 50
Airline1 Summer 65
Airline1 Summer 40
Airline2 Winter 55
Airline2 Winterindia 105
Airline3 Winterlove 110
Airline3 Winter 111
Airline4 Winter 105
Airline4 Winteruk 95

query would return:

Airline1, Summer, 40
Airline2, Winter, 55
Airline4, Winteruk, 95
Airline3, Winterlove, 110

MIN(price) group only airline and other field return normal , same fr min price row.