SQL query logic, adding rows' fields together

I am working on a web app and need to total a field an array of records.

To further explain please look at the included image:

This image depicts my output from using the following query:

SELECT * FROM logbook WHERE dayNight = 'D'

Now what I need to do is total all the values in the hours field of all these records.

Once this is done, the output should be something of the following (just as an example):

6:05:21

Representing HH:MM:SS.

But I would like to represent it in just hours and mins, in a decimal value... 开发者_如何学PythonSo if I had 6 hours and 40 mins and 2 seconds, it would say jsut 6:40. This would be the following structure HH:MM.

So I don't know how to do any of this - but thank you for all the help!

You can most of the way with a whole bunch of nasty casting:

select cast(sum(cast(hours as time)) as time)
from logbook
where dayNight = 'D'

That will give you a result as a time value. Then mix in a time_format to get the desired precision:

select time_format(cast(sum(cast(hours as time)) as time), '%H:%i')
from logbook
where dayNight = 'D'

You should change your hours column from varchar to time.

You can get away with just this:

select time_format(sum(cast(hours as time)), '%H:%i')
from logbook
where dayNight = 'D'

If you want to avoid the extra casting; however, leaving the outer casting in might make things easier to debug later as just sum(cast(hours as time) produces things like 234211 for 23:42:11.

Once your columns have been converted to sensible time columns, then you can do away with all the casting:

select time_format(sum(hours), '%H:%i')
from logbook
where dayNight = 'D'

or to sum the all regardless of dayNight:

select time_format(sum(hours), '%H:%i')
from logbook