+- FsPassengers Forums (http://www.fspassengers.com/forum)
+-- Forum: FsPassengers (http://www.fspassengers.com/forum/forumdisplay.php?fid=3)
+--- Forum: FsPassengers Support (http://www.fspassengers.com/forum/forumdisplay.php?fid=5)
+--- Thread: help with MySQL and 'flights' table (/showthread.php?tid=6396)
help with MySQL and 'flights' table - AltairZQ - 27-06-2006
When I execute this query, which is copied from the fsplistcfg.php script:
SELECT `DepartureIcaoName`, CONCAT( SUM( FlightResult LIKE 'Perfect' ) / COUNT( `DepartureIcaoName` ) *100, '%' ) AS TFlightPerfect FROM `flights`
GROUP BY `DepartureIcaoName` ORDER BY TFlightPerfect DESC
This is the result:
DepartureIcaoName TFlightPerfect
LECO - A Coruna - Spain 88.8889
LEAL - Alicante - Spain 85.7143
LEGR - Granada - Spain 83.3333
LEMG - Malaga - Spain 72.7273
GCHI - Hierro - Canary Islands 66.6667
LEIB - Ibiza - Spain 50.0000
CYYT - St Johns Intl - Canada 50.0000
ENGM - Oslo Gardermoen - Norway 50.0000
LEBL - Barcelona - Spain 50.0000
LELL - Sabadell - Spain 25.0000
LXGB - Gibraltar - Gibraltar 100.0000
BIRK - Reykjavik - Iceland 100.0000
GCLP - Gran Canaria - Canary Islands 100.0000
LEGE - Girona - Spain 100.0000
LFPG - Charles De Gaulle - France 100.0000
LEBZ - Talavera La Real - Spain 100.0000
CYUL - Montreal Intl Dorval - Canada 100.0000
LEPA - Palma De Mallorca - Spain 100.0000
KSLC - Salt Lake City Intl - Usa 0.0000
RCTP - Chiang Kai Shek Intl - Taiwan 0.0000
LETO - Torrejon - Spain 0.0000
RJAA - New Tokyo Intl - Japan 0.0000
EDDP - Leipzig Halle - Germany 0.0000
As you can see, 100 (per cent) is almost at the bottom, just before 0 (per cent) when it should be the first on the list
If I use ORDER BY ASC, the first flights to appear are the ones with 0%, then the 100% and then the 25%, 50%, 66,6667%.. ascending.
¿How can I get a list in descending order with 100% at the top and 0% at the bottom?
Re: help with MySQL and 'flights' table - poden - 27-06-2006
In the ORDER BY line, TFlightPerfect isn't within quotes?
Re: help with MySQL and 'flights' table - eazy - 27-06-2006
It's because the CONCAT function returns a string instead of a numeric value, so '8' (in '88') is higher then '1' (in '100'). Just leave out the
CONCAT thing. If you want to place a "%" sign after the value do it the moment where you display the result, i.e.:
echo TFlightPerfect_NumericValue . '%';
Hope it helps.
Re: help with MySQL and 'flights' table - AltairZQ - 28-06-2006
It was exactly what you suggested!
This is working perfectly now:
SELECT `DepartureIcaoName`, SUM( FlightResult LIKE 'Perfect' ) / COUNT( `DepartureIcaoName` ) *100 AS TFlightPerfect FROM `flights`
GROUP BY `DepartureIcaoName` ORDER BY TFlightPerfect DESC
I tried removing the '%' but left the CONCAT instruction there and I guess it still changed it to string.
Many thanks for your help.
Regards.
Re: help with MySQL and 'flights' table - AltairZQ - 01-07-2006
Quote:poden wrote:
In the ORDER BY line, TFlightPerfect isn't within quotes?
I think the quotes are only needed when there are spaces inside the filed name.
Sorry didn't see your message before, went straight to the bottom of the page and since it solved the problem I was too much in a hurry to put it in
my web
Re: help with MySQL and 'flights' table - eazy - 01-07-2006
The quotes are a specialty of mySql. In SQL language you don't need quotes for field names - and you can leave them away in mySql as well,
but if you set them it doesn't matter. Field names with blanks are not allowed anyway.
Re: help with MySQL and 'flights' table - AltairZQ - 02-07-2006
Quote:eazy wrote:
The quotes are a specialty of mySql. In SQL language you don't need quotes for field names - and you can leave them away in mySql as well,
but if you set them it doesn't matter. Field names with blanks are not allowed anyway.
Yes they are, I use some aircraft names, that need special certification in our VA, as field names in the user_profile table, and they have blanks and
the "-" character. The only thing is you need to use the "`" character to quote them, otherwise MySQL will get the first characters until the first
blank as the field name.