Select question

[somle]

I have a problem wich I dont understand..

---

"SELECT p.FirstName, p.LastName, p.PilotID, p.Email,
SEC_TO_TIME(SUM(TIME_TO_SEC(l.ArrivalTime)-TIME_TO_SEC(l.DepartureTime)
+ IF(TIME_TO_SEC(l.DepartureTime)>TIME_TO_SEC(l.ArrivalTime),246060,0))) AS pHours
FROM Pilots p LEFT JOIN Log l ON p.PilotID = l.PilotID GROUP BY PilotID ORDER BY pHours DESC";

---

The point with this query is to get out a list with Pilots where the pilots who have flown most hours will be on the top. But thats not happening, the pilots with below 100 hours will be correct placed in the list, but the pilots who have over 100 hours will not be on the top!

Anyone could please help me and tell me why??

[kevinsequeira]

the error maybe because its not treating the variable as numeric

so "121" will be less than "98" if they are non-numeric, because it will check if "121" is greater than "98"

i could be wrong, just a thought ...
reg
kevin

[tomhath]

I don't understand the outermost SEC_TO_TIME for the pHours calculation. Shouldn't you just divide the SUM by (60*60) to get hours, instead of converting to a time?

[somle]

Originally posted by kevinsequeira
the error maybe because its not treating the variable as numeric

so "121" will be less than "98" if they are non-numeric, because it will check if "121" is greater than "98"

i could be wrong, just a thought ...
reg
kevin

So how do I do to treat it numeric ?

[nnichols]

The easiest way to do it is as tomhath said - remove the outer SEC_TO_TIME and divide by 3600 instead. This will give you the time in hours with the mm:ss expressed as a decimal which will sort numerically.

[somle]

I still dont get it how I can divde it with 3600.

Because the numbers in the database is:

DepartureTime ArrivalTime
07:20:00 07:55:00

So how do i get this to be 0:35 ?

[nnichols]

SELECT p.FirstName, 
	p.LastName, 
	p.PilotID, 
	p.Email, 
	SUM(TIME_TO_SEC(l.ArrivalTime)-TIME_TO_SEC(l.DepartureTime) 
		+ IF(TIME_TO_SEC(l.DepartureTime)>TIME_TO_SEC(l.ArrivalTime),24*60*60,0))/3600 AS pHours 
FROM Pilots p 
LEFT JOIN Log l ON p.PilotID = l.PilotID 
GROUP BY PilotID 
ORDER BY pHours DESC

This will return the time in hours with the mins & secs expressed as a decimal fraction instead of hh:mm:ss. You could easily write a simple function to translate this back into hh:mm:ss if needs be, or you could select this data twice - first as a decimal for sorting and again as hh:mm:ss for display purposes -

SELECT p.FirstName, 
	p.LastName, 
	p.PilotID, 
	p.Email, 
	SUM(TIME_TO_SEC(l.ArrivalTime)-TIME_TO_SEC(l.DepartureTime) 
		+ IF(TIME_TO_SEC(l.DepartureTime)>TIME_TO_SEC(l.ArrivalTime),24*60*60,0)) AS pHours, 
	SEC_TO_TIME(SUM(TIME_TO_SEC(l.ArrivalTime)-TIME_TO_SEC(l.DepartureTime) 
		+ IF(TIME_TO_SEC(l.DepartureTime)>TIME_TO_SEC(l.ArrivalTime),24*60*60,0))) AS disp_time
FROM Pilots p 
LEFT JOIN Log l ON p.PilotID = l.PilotID 
GROUP BY PilotID 
ORDER BY pHours DESC