CODEWITHSUNDEEP
oracle-databasetimesumtimestamp
Juan Menashsheh
Juan Menashsheh

Reputation: 53

Calculate sum time in Oracle

I have this table in Oracle, email and time are varchar.

Email               Time
----------------    -----------
[email protected]     00:10:40
[email protected]     00:40:10
[email protected]     01:10:20
[email protected]     00:43:40
[email protected]     00:42:40
[email protected]    00:30:40
[email protected]    00:54:10
[email protected]    00:47:40
[email protected]      00:50:40
[email protected]      01:05:40
[email protected]      00:45:40
[email protected]      00:51:40
[email protected]      00:36:40

I want to get sum of the time and group by email. Is it possibile in Oracle?

UPDATES

Time is duration.

Upvotes: 3

Views: 5592

Answers (1)

San
San

Reputation: 4538

Use this query 

SQL> WITH table_(Email, Time) as (
  2      select '[email protected]', '00:10:40' from dual union all
  3      select '[email protected]', '00:40:10' from dual union all
  4      select '[email protected]', '01:10:20' from dual union all
  5      select '[email protected]', '00:43:40' from dual union all
  6      select '[email protected]', '00:42:40' from dual union all
  7      select '[email protected]', '00:30:40' from dual union all
  8      select '[email protected]', '00:54:10' from dual union all
  9      select '[email protected]', '00:47:40' from dual union all
 10      select '[email protected]', '00:50:40' from dual union all
 11      select '[email protected]', '01:05:40' from dual union all
 12      select '[email protected]', '00:45:40' from dual union all
 13      select '[email protected]', '00:51:40' from dual union all
 14      select '[email protected]', '00:36:40' from dual )
 15  ---------------------
 16  -- End if sample data
 17  ---------------------
 18  SELECT email, numtodsinterval(sum(SUBSTR(TIME, 1, 2)*3600 + SUBSTR(TIME, 4, 2)*60 + SUBSTR(TIME, 7, 2)), 'SECOND') total_duration
 19    FROM table_
 20   GROUP BY email;

Output:

EMAIL            TOTAL_DURATION
---------------- --------------------------------------------------------------------------------
[email protected] +000000000 02:12:30.000000000
[email protected]   +000000000 04:10:20.000000000
[email protected]  +000000000 03:27:30.000000000

So, your query is

SELECT email, numtodsinterval(sum(SUBSTR(TIME, 1, 2)*3600 + SUBSTR(TIME, 4, 2)*60 + SUBSTR(TIME, 7, 2)), 'SECOND') total_duration
  FROM table_
 GROUP BY email;

Upvotes: 6

Related Questions