CODEWITHSUNDEEP
sqloracle-database
Max
Max

Reputation: 61

Is it possible to use a SUM query on INTERVAL DAY TO SECOND datatypes?

Is it possible to use a SUM query to give me the results of adding two columns together? These columns use the INTERVAL DAY TO SECOND datatype and I want to add the time from both columns to give me a total time.

Thank you for reading!

Upvotes: 2

Views: 8484

Answers (2)

ruakh
ruakh

Reputation: 183240

SUM is for aggregating multiple rows, adding values together. To add two columns from the same row, you use +. For example, given a table T with this data:

 A | B
---+---
 1 | 2
 3 | 4

You can write SELECT SUM(a), SUM(b) FROM t to get a single row that uses addition to aggregate the rows of T:

 SUM(A) | SUM(B)
--------+--------
      4 |      6

or SELECT a + b FROM t to add the two columns together:

 A+B
-----
   3
   7

or either SELECT SUM(a) + SUM(b) FROM t or SELECT SUM(a+b) FROM t (as you prefer) to combine the two effects:

 SUM(A)+SUM(B)                 SUM(A+B)
---------------               ----------
            10                       10

For INTERVAL DAY TO SECOND, the situation is a bit trickier, because the SUM function only works for "any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype". So you have to do some conversions back and forth. Given this table:

 A                  | B
---------------------+-------------------
 +1 01:01:01.000001 | +2 02:02:02.000002
 +3 03:03:03.000003 | +4 04:04:04.000004

this query:

SELECT NUMTODSINTERVAL(SUM(EXTRACT(DAY FROM a + b)), 'DAY') +
       NUMTODSINTERVAL(SUM(EXTRACT(HOUR FROM a + b)), 'HOUR') +
       NUMTODSINTERVAL(SUM(EXTRACT(MINUTE FROM a + b)), 'MINUTE') +
       NUMTODSINTERVAL(SUM(EXTRACT(SECOND FROM a + b)), 'SECOND') AS "SUM(A+B)"
  FROM t
;

will give this result:

 SUM(A+B)
---------------------
 +10 10:10:10.000010

Upvotes: 3

DazzaL
DazzaL

Reputation: 21973

you can add intervals, no problem:

SQL> select a, b, a+b from t;

A                    B                    A+B
-------------------- -------------------- -----------------------------
+01 00:00:00.000000  +00 00:05:00.000000  +000000001 00:05:00.000000000
+02 00:00:00.000000  +00 00:50:00.000000  +000000002 00:50:00.000000000
+02 00:14:24.000000  +00 00:50:12.000000  +000000002 01:04:36.000000000

Upvotes: 1

Related Questions