SQL sum(time) as time column does not sort in ASC and DESC order

Question

I have been trying to sort the result of my SQL query but it's not working properly, I have tried other solutions, but not worked, reference link

My Query

SELECT tournament_entry.TE_club, MAX(tournament_entry.TE_pegion) As TE_pegion, 
       concat(
       floor(SUM(TIME_TO_SEC(tournament_entry.TE_flytime))/3600),':',
       floor(SUM( TIME_TO_SEC(tournament_entry.TE_flytime))/60)%60,':',
       SUM(TIME_TO_SEC(tournament_entry.TE_flytime))%60) as TE_flytime
  FROM tournament_entry  
  WHERE  tournament_entry.TE_tournament = '$Tname'  
  GROUP BY TE_club 
  ORDER BY TE_flytime DESC

Point 1, I have used CONCAT(), the reason to display the sum of time greater than 838:59:59 reference link

Step 1 followed,

ORDER BY TE_flytime ASC

results, which is not correct

Step 2 followed,

ORDER BY TE_flytime DESC

results, not correct, in this way it takes results greater than 1000 to the bottom (last), maybe if there is any logic or way to use and it will sort it properly.

Step 3 followed,

ORDER BY 'cast(TE_flytime as float) time'

results, not correct, mixed up all results. reference link

NOTE: I want to sort it in a way that it displays the greatest result on top and so on

e.g

1034:46:0
1027:4:0
965:37:0
809:11:0

does this help: https://stackoverflow.com/questions/9511882/sorting-by-date-time-in-descending-order — Martin, Jun 17 '20 at 13:27
Why are you sorting only by one attribute? Use three attributes and it will work as you want. — XraySensei, Jun 17 '20 at 13:28
@Martin Server version: 10.3.23-MariaDB, and i have tried the above-mentioned link nothing helped — Jahanzeb Nawaz, Jun 18 '20 at 10:49
@XraySensei cos I don't need to display extra fields on result and result is depended on Total Fly Time (TE_flytime) — Jahanzeb Nawaz, Jun 18 '20 at 10:51

score 2 · Accepted Answer · answered Jun 17 '20 at 14:03

2

The calculated column TE_flytime is a string so if you sort by that column the sorting will be alphabetical.
You can order by the total number of seconds:

ORDER BY SUM(TIME_TO_SEC(tournament_entry.TE_flytime)) DESC

answered Jun 17 '20 at 14:03

forpas

160,666
10
38
76

SQL sum(time) as time column does not sort in ASC and DESC order

1 Answers1