2

I have a column called duration_d which is varchar2 and the data in that table looks like below

duration_d
-----------
12:25
01:35
12:10
04:21
12:18
12:24

I tried below query 

SELECT SUM( to_date( duration_d, 'mi:ss' ))
  FROM table 
 GROUP BY calling_number;

When I execute it following error is coming

ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

can any one tell me how to make sum it?

RealCheeseLord
  • 785
  • 1
  • 12
  • 24
bharathRaj
  • 25
  • 8
  • you can't use sum with dates. What values are you expecting when you sum those durations? Ps:one other thing: if you want to use to_date, you have to add the rest of the information, not just the duration: ex: to_date('2000-01-01 00:'||dur,'yyyy-mm-dd hh24:mi:ss') – Renato Afonso Aug 03 '17 at 08:28
  • Possible duplicate of [Calculate the Sum of duration in sql query](https://stackoverflow.com/questions/891833/calculate-the-sum-of-duration-in-sql-query) – J-Alex Aug 03 '17 at 08:35

4 Answers4

3

To get the total as fractions of a day you can use:

SELECT SUM( TO_DATE( duration_d, 'MI:SS' ) - TO_DATE( '00:00', 'MI:SS' ) ) AS total
FROM   your_table

Which gives the result:

TOTAL
------------------------------------------
0.0383449074074074074074074074074074074074

To convert this to an interval data type you can use NUMTODSINTERVAL:

SELECT NUMTODSINTERVAL(
         SUM( TO_DATE( duration_d, 'MI:SS' ) - TO_DATE( '00:00', 'MI:SS' ) ),
         'DAY'
       ) AS total
FROM   your_table

Which gives the result:

TOTAL
-------------------
+00 00:55:13.000000
MT0
  • 143,790
  • 11
  • 59
  • 117
0

Please try below:

with x as 
(select sum((regexp_substr(YOUR_COLUMN, '[0-9]+', 1, 1)*60) + 
             regexp_substr(id, '[0-9]+', 1, 2)) seconds 
from YOUR_TABLE)
SELECT 
    TO_CHAR(TRUNC(seconds/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD(seconds,3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD(seconds,60),'FM00')
FROM x

Will work only if the duration is always [MI:SS].

Also you can add the group by as per your requirement.

Converting Seconds to the required duration format Reference.

Group By

with x as 
(select calling_number,sum((regexp_substr(YOUR_COLUMN, '[0-9]+', 1, 1)*60) + 
             regexp_substr(id, '[0-9]+', 1, 2)) seconds 
from YOUR_TABLE
group by calling_number)
SELECT calling_number, 
    TO_CHAR(TRUNC(seconds/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD(seconds,3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD(seconds,60),'FM00')
FROM x
pOrinG
  • 896
  • 3
  • 13
  • 27
0

Use a combination of SUBSTR, to_char, to_date, NVL, INSTR, reverse and SUM.

SELECT "calling_number",
to_char(to_date(SUM(NVL(SUBSTR("duration_d", 0, INSTR("duration_d", ':')-1), "duration_d"))*60 +
SUM(substr("duration_d", - instr(reverse("duration_d"), ':') + 1)),'sssss'),'hh24:mi:ss') AS SUM_DURATION_D
FROM yourtable
GROUP BY "calling_number"

Output

calling_number  SUM_DURATION_D
1               00:26:10
2               00:29:03

SQL Fiddle: http://sqlfiddle.com/#!4/9b0a81/33/0

Matt
  • 14,906
  • 27
  • 99
  • 149
  • thank you but i want to do group by calling number group by is not working on it – bharathRaj Aug 03 '17 at 08:51
  • ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number. – bharathRaj Aug 03 '17 at 08:55
  • You are multiplying the sum of the minutes by 60 and adding the first character of the sum of the seconds divided by 60 for the first number ... it won't work. You do not need to multiply he minutes by 60 and if the seconds sum to a 4-or-more digit number then you are going to get an incorrect answer. You would do better using `TRUNC` or `FLOOR` and `MOD` to get the minute and second components of the sum of the seconds. – MT0 Aug 03 '17 at 09:03
  • @MT0 your right, i have re done my answer to allow for any amount of values and also fixed their group by issue – Matt Aug 03 '17 at 09:17
  • @bharathRaj try my new anwser – Matt Aug 03 '17 at 09:18
  • Or simply: `TO_CHAR( TO_DATE( SUM( SUBSTR( duration_d, 1, 2 ) * 60 + SUBSTR( duration_d, 4, 2 ) ), 'sssss' ), 'hh24:mi:ss' )` *(although it will still give an incorrect answer if the durations sum to more than 24 hours)* – MT0 Aug 03 '17 at 09:25
  • Thats assuming the minutes will never be bigger than 2 characters, which is why i used the substr instr combo – Matt Aug 03 '17 at 09:26
  • If you want to support 3 or more digit minutes then: `TO_CHAR( TO_DATE( SUM( SUBSTR( duration_d, 1, INSTR( duration_d, ':' ) - 1 ) * 60 + SUBSTR( duration_d, -2 ) ), 'sssss' ), 'hh24:mi:ss' )` – MT0 Aug 03 '17 at 09:32
-1

Correct spelling as below

SELECT SUM( TO_DATE( duration_d, 'mi:ss' ) )
 FROM YOURTABLE Group By calling_number
LifeOfPi
  • 625
  • 5
  • 19