[RESOLVED] Time & Time again

[UKlee]

Hi all Im stuck with time in php infact I am begining to think this is my nemisis.

Is it possible to select all time from a database table and add this all together.

For example

i have two types of aircraft with different flighttimes for each how would i call this to enable me to grab the time, for say aircraft and add this all together and do the same for aircraft 2 and so on untill it had gathered al aircraft and its time and added them all up for each.

aircraft 1 has perhaps 2 entries in table

01:00:00 and 02:00:00 the total for aircraft 1 is 03:00:00

but aircraft 2 has 3

01:00:00 and 02:00:00 and 02:00:00 total 05:00:00

i would like to show the results in a table I know you can sum() but i do not think this works for time.

I hope you can see what I am trying to achieve here but im unsure of how to abtain the result.

i was going to call like select distinct (aircraft),time where blah blah

but from this point im unsure.or perhaps completely incorrect with my first call.

any help or advice would be great ,thanks for taking the time to read this post. all the best

Lee

[jheitz]

You don't want DISTINCT (time) as this would mean that two "1 hour" entries for a certain airplane will be reduced to 1 (not what you want).

You need something like SUM. Surprised this does not work, have you tried it? What is your exact data type and what Database System are you using?

Basically you would need to do a select grouping by airplane and getting some kind of SUM on the times. This might be achieved by converting the TIME to a different type if TIME is giving you a headache when summing up values

[Installer]

This should work:

SELECT aircraft, SEC_TO_TIME(SUM(TIME_TO_SEC(flight_time))) AS total_time FROM my_table GROUP BY aircraft

[UKlee]

Thank you for your responses I have used the code shown by Installer and this works fine for what it is I am trying to do .

thank you both very much for taking the time all the best

Lee