x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 537

# Format SUM function

Greetings,

I have a database that has a field which contains seconds.  I am doing a Group By and using SUM on the seconds field.  I need to format the SUM results into HH:MM:SS.

Select Sum([Field1]),
Sum([Seconds]) AS MyDuration
Group By [Field3]
Where [Field4] = 'ABC'

MyDuration I need in HH:MM:SS

Thanks so much
0
jchauncey60
• 2
2 Solutions

Commented:
Try this:

``````SELECT q.SumOfField1, Format(Int(q.[MyDuration]/3600),"00") & ":" & Format(Int((q.[MyDuration]-(Int(q.[MyDuration]/3600)*3600))/60),"00") & ":" & Format(((q.[MyDuration] Mod 60)),"00") AS ElapsedTime
FROM
(
Sum([Field1]) AS SumOFField1,
Sum([Seconds]) AS MyDuration
Group By [Field3]
Where [Field4] = 'ABC'
) q
``````
0

Commented:
If that value (Sum([Seconds]) is not going to exceed 24 hours, you could also do:

Format(Sum([Seconds])/86400, "hh:nn:ss")

dividing the sum by 86400 (the number of seconds in a day) will give you the decimal equivalent of a time value as stored in the database.
0

Author Commented:
Solution #2 works for this problem.  Solution #1 is the best for generic applications. Thanks for the quick response.
0

Commented: