Re: Summary with Group-by function on time (date-field)

From: Knut Talman <knut.talman_at_mytoys.de>
Date: Wed, 24 Apr 2002 14:43:35 +0200
Message-ID: <3CC6A877.6090007@mytoys.de>

Thomas Stuefer wrote:

> I use Oracle 8.1.7.2.7 and have a simple table:
>
> user VARCHAR2(30)
> duration DATE
>
>
> The data in this table looks like:
>
> USER DURATION
> ------------------------------------------------
> SCOTT 01.04.2002 01:30:00
> ADAMS 01.03.2002 00:45:00
> MILLER 01.04.2002 01:35:00
> SCOTT 01.04.2002 00:50:00
> SCOTT 01.02.2002 00:30:00
> MILLER 01.03.2002 00:10:00
> ADAMS 01.04.2002 00:20:00
> MILLER 01.02.2002 00:30:00
>
>
> How can I get the group-summary of duration on every user:
>
> USER SUM_DURATION
> ------------------------------------------------
> ADAMS 01:05:00
> MILLER 02:15:00
> SCOTT 02:50:00
>
>
> Remark: The date in the field "duration" is not important and can be
> ignored.

The only way I know to get the result is to sum seconds, minutes and hours and concatenate the result. Of course summing seconds or minutes will eventually have a result > 60. That's why we have to divide by 60 and add to hours (or minutes). The example assumes that seconds are always 0.

select user, to_char(sum(to_number(to_char(duration,'HH24'))) +

          trunc(sum(to_number(to_char(duration,'MI')))/60))
          ||':'||to_char(mod(sum(to_number(to_char(duration,'MI'))),60))||':00'
        as duration

from test
group by user;

It takes a while to compute it...
I am also very curious how many better/easier solutions are out there.

Regards,

Knt Received on Wed Apr 24 2002 - 07:43:35 CDT