I have a query such that the query's result is:
SELECT CONVERT(VARCHAR(8),(MAX(END_TIME)-MIN(START_TIME)),108) as DURATION WHERE ... GROUP BY TITLE
For each title, DURATION
is different. The query returns:
DURATION
00:16:14
00:00:00
00:01:30
00:16:25
00:09:34
00:00:01
01:04:04
00:00:28
00:00:12
00:06:11
00:26:38
00:31:44
00:02:16
00:03:22
00:09:39
00:03:20
00:03:43
00:09:33
00:08:05
00:06:58
00:25:29
01:55:30
00:03:02
00:00:18
00:06:09
00:07:26
00:25:43
00:00:16
00:26:58
02:09:38
00:57:56
00:00:45
00:00:00
00:07:24
00:00:54
00:00:27
00:01:28
00:07:14
00:00:19
01:43:25
00:58:23
00:02:29
02:19:48
00:09:06
05:12:15
02:27:15
00:56:47
00:02:24
I need the sum of these values; how can I get it?
The SQL AGGREGATE SUM() function returns the SUM of all selected column. Applies to all values. Return the SUM of unique values. Expression made up of a single constant, variable, scalar function, or column name.
You cannot sum times. What you should do instead is use the DateDiff function with your start and end time using the seconds parameter. This will return an integer data type which you can SUM on. When you're done, convert it to a DateTime to format it like hours:minutes:seconds.
Convert minutes into seconds
SUM()
the seconds
Convert back to minutes
The following will give you the SUM of seconds:
SET @Seconds = SELECT SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME]))
The following then turns that into a datetime
object:
select convert(varchar(8), dateadd(second, @Seconds, 0), 108)
Or as 1 query:
SELECT convert(varchar(8), dateadd(second, SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME])), 0), 108)
You cannot sum times. What you should do instead is use the DateDiff function with your start and end time using the seconds parameter. This will return an integer data type which you can SUM on. When you're done, convert it to a DateTime to format it like hours:minutes:seconds.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With