how to sum time column using group by

[Sathia]

Hi

I need to sum a column using group by. the data type of this column is
date/time.
Iam storing time in this column as hh:mm.

I need a query to sum this column

eg.,
Table Xyz
Month Time
1 1:30
1 1:00
2 2:00

Expected output

Month Time
1 2:30
2 2:00

Please help me

Thanks and regards
Sathia

 

[Stefan Hoffmann]

hi Sathia,

I need to sum a column using group by. the data type of this column is
date/time.
Iam storing time in this column as hh:mm.
Table Xyz
Month Time
1 1:30
1 1:00
2 2:00

SELECT [Month], Sum(CDbl([Time]) - CInt([Time])) As T
FROM Xyz
GROUP BY [Month]

CDbl([Time]) - CInt([Time]) is necessary to strip any date part.
The result is:

Days: CInt([T])
Time: CDate(CDbl([T]) - CInt([T]))


mfG
--> stefan <--

 

[Rick Brandt]

Hi

I need to sum a column using group by. the data type of this column is
date/time.
Iam storing time in this column as hh:mm.

I need a query to sum this column

When people see the expression 1:30 it could either mean the *time of day* or
the amount of time one hour and 30 minutes. In a Database DateTime field it
ALWAYS means the *time of day*. As such, performing math on them makes as much
sense as dividing Thursday by January.

It is true that under the covers DateTimes in Access/Jet are stored as numbers
and this allows certain kinds of math operations to be performed, but simply
adding them together falls apart as soon as you cross the midnight boundary when
the time wraps around and the DAY is incremented.

To store time intervals you should store them as integers that represent the
number of hours or minutes or seconds, etc.. and then you can perform whatever
math you like. The results can then be converted to the hh:nn:ss format for
display.

 

[Jason Lepack]

Assuming that your time is a date/time variable, formatted as a short
time, then this should work:

SELECT
[Month],
Format(Sum([Time]),"Short Time") AS TotalTime
FROM
YourTable
GROUP BY
[Month]

Cheers,
Jason Lepack

 

[Dennis]

Assuming you have your query with your month as column 1 and time as column 2.
Column 1 is set to group by and column 2 is set to Sum.
Right click the column time in your query grid and then select properties.
In the format property, type in Short Time
Close the field properties box and then save and run your query

 

[John Spencer]

A DateTime field is designed to store a point in time (It is now 1:22 AM on
Dec 1, 2007) and not a duration ( I worked for an hour and 22 minutes). You
are better off storing the duration as a number of minutes or seconds and
then adding that. Working with your current data and the assumption that
the field always stores the time only (it actually stores the time and the
zero date), you should be able to use something like the following

SELECT [Month]
, Sum([Time]*24*60) as Minutes
, Sum([Time]*24*60) \ 60 as Hours
, Sum([Time]*24*60) \ 60 & ": " & Format(Sum([Time]*24*60) Mod 60,"00") as
HoursMinutes
FROM XYZ
GROUP BY [Month]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dennis]

Further to my earlier answer, It only works as long as the total does not
exceed 24 hours.

 

[Sathia]

Y.........Eah it works
Thanks Jason Hats Off to you!


Regards
Sathia

Assuming that your time is a date/time variable, formatted as a short
time, then this should work:

SELECT
[Month],
Format(Sum([Time]),"Short Time") AS TotalTime
FROM
YourTable
GROUP BY
[Month]

Cheers,
Jason Lepack

Hi

I need to sum a column using group by. the data type of this column is
date/time.
Iam storing time in this column as hh:mm.

I need a query to sum this column

eg.,
Table Xyz
Month Time
1 1:30
1 1:00
2 2:00

Expected output

Month Time
1 2:30
2 2:00

Please help me

Thanks and regards
Sathia