?
Solved

SQL sum Function in Oracle

Posted on 2007-10-01
21
Medium Priority
?
511 Views
Last Modified: 2013-12-19
I have a table that has Tracking_start_time and Tracking_end_time. I need to find Average tracking time.

What I need to do is Subtract Start time from end time and do a sum om it.

With one Query I can get sum and with one query I can get count of records. Dividing will give me average tracking time per transaction.

Currently I'm using Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
But I'd like the qurey to be simple SQL so that it's compatible tihe SQL Server, MySQL and DB2

I tried this

Select SUM(TRACKING_END_TIME - TRACKING_START_TIME) as thetime FROM TRACKING_SUMMARY

butI got an error ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

I think we need to convert time to seconds then do the math but how??? help please
0
Comment
Question by:aliasim99
  • 9
  • 9
  • 2
  • +1
21 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 19992501
SUM(DATEDIFF(mi, TRACKING_START_TIME, TRACKING_END_TIME)) will give you a sum of minutes (use hh for hours, ss for seconds)

I am not sure if Oracle has DATEDIFF function, but should have something similar. Summing an end time minus a start time isn't making sense to Oracle as it wouldn't to SQL Server
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19993171
Can you describe your table for us?
What are the datatypes for the two tracking_%_time columns?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 19993228
From what I can tell, your two dates are TIMESTAMP types on the Oracle side.

This should give you what you are looking for to the level of a minute:

select sum(trunc(tracking_end_time, 'mi') - trunc(tracking_start_time, 'mi')) thetime from tracking_summary
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:aliasim99
ID: 19993248
SUMMARY_ID                     NOT NULL NUMBER                                                                                                                                                                                        
TRACKING_ID                    NOT NULL VARCHAR2(128)                                                                                                                                                                                
TRACKING_STATE                 NOT NULL VARCHAR2(255)                                                                                                                                                                                
SENDER_ID                      NOT NULL VARCHAR2(50)                                                                                                                                                                                  
RECEIVER_ID                    NOT NULL VARCHAR2(50)                                                                                                                                                                                  
TRANSACTION_TYPE               NOT NULL VARCHAR2(50)                                                                                                                                                                                  
DOC_REFERENCE_NUMBER                    VARCHAR2(255)                                                                                                                                                                                
TRACKING_START_TIME                     TIMESTAMP(6)                                                                                                                                                                                  
TRACKING_END_TIME                       TIMESTAMP(6)                                                                                                                                                                                  
TRACKING_PURGE_TIME            NOT NULL TIMESTAMP(6)                                                                                                                                                                                  
TRACKING_RECORD_OWNER                   VARCHAR2(50)                                                                                                                                                                                  
ACCESS_CODE                             VARCHAR2(50)                                                                                                                                                                                  
COMPLETE_TRACK_STEP                     NUMBER
0
 

Author Comment

by:aliasim99
ID: 19993327
johnsone: This is the result I get. . Can you explain this to me.


THETIME                
----------------------
0.009722222222222222222222222222222222222222

1 rows selected
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19993458
I don't see what is it that you're trying to sum. You need to values to be summed, so the below expression should work:

Select TRACKING_END_TIME - TRACKING_START_TIME as thetime FROM TRACKING_SUMMARY
/
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19993463
I meant: You need TWO values to be summed
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 200 total points
ID: 19993583
That number is the number of days difference.

If you multiply by 24 you get the number of hours.
If you multiply by 1440 you get the number of minutes.
If you multiply by 86400 you get the number of seconds.





Select TRACKING_END_TIME - TRACKING_START_TIME as thetime FROM TRACKING_SUMMARY;

would give you the difference for each record (1 row returned for each row in the table), while

select sum(trunc(tracking_end_time, 'mi') - trunc(tracking_start_time, 'mi')) thetime from tracking_summary;

would give you the sum of the difference for all records (1 row returned).
0
 

Author Comment

by:aliasim99
ID: 19993608
OK let me explain again what I need. These are EDI Documents. We track them while they go through translation. Each row has Track Start (the time when system gets EDI doc) and end time (When doc leaves the sys). Now subtracting those will give us how much time it took to process one transaction. I have more than 50,000 records at time. We need to find out what is the average processing time for Transations. It is usually between 5-10 Sec.

So I need End - Start time in seconds dived by total number of records = average time for a transaction.

Now may be I'm not using the Sum function in the right way. But this is what I want to achieve. Get End - Start and add those all up for all records.  
0
 

Author Comment

by:aliasim99
ID: 19993691
Johnsone is on the right track that is exactly what I need. But Looks like I'm not getting the right number

SELECT TRACKING_START_TIME - TRACKING_END_TIME thetime
FROM TRACKING_SUMMARY where COMPLETE_TRACK_STEP = 4;

THETIME    
-----------
0 0:14:2.910000000

1 rows selected

select sum(trunc(TRACKING_START_TIME, 'mi') - trunc(TRACKING_END_TIME, 'mi'))  thetime
FROM JCI_TRACKING_SUMMARY where COMPLETE_TRACK_STEP = 4;

THETIME                
----------------------
0.009722222222222222222222222222222222222222

1 rows selected

select sum(trunc(TRACKING_START_TIME, 'mi') - trunc(TRACKING_END_TIME, 'mi'))*86400  thetime
FROM TRACKING_SUMMARY where COMPLETE_TRACK_STEP = 4;

THETIME                
----------------------
840                    

1 rows selected

Unfortunately there is only one record right now that has Complete_track_step = 4 so that means sum should be equal to the result of that one row. Under Developemt DB I only have one row to work with.. Kinda sucks and a stupid way to test function but that's how it is. in the Production DB It have more than 50K records.

I was expecting result 1 and 3 to be close.. like 14 min and some sec. I know I'm doing Start - End That's because  Dev DB has them inverted.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19993744
Follow this example:

ALEX@DEV > SELECT * FROM TIME_TRACKING;

TRACKING_START_TIME                                                         TRACKING_END_TIME
--------------------------------------------------------------------------- -----------------------------
01-SEP-07 02.25.03.101000 PM                                                01-SEP-07 02.55.25.210000 PM
01-SEP-07 04.25.03.101000 PM                                                01-SEP-07 04.35.25.210000 PM

ALEX@DEV > SELECT TRACKING_END_TIME-TRACKING_START_TIME FROM TIME_TRACKING;

TRACKING_END_TIME-TRACKING_START_TIME
---------------------------------------------------------------------------
+000000000 00:30:22.109000
+000000000 00:10:22.109000

  1  SELECT SUM(
  2  EXTRACT(SECOND FROM TRACKING_END_TIME)
  3  -
  4  EXTRACT(SECOND FROM TRACKING_START_TIME)
  5  )
  6* FROM TIME_TRACKING
ALEX@DEV > /

SUM(EXTRACT(SECONDFROMTRACKING_END_TIME)-EXTRACT(SECONDFROMTRACKING_START_TIME))
--------------------------------------------------------------------------------
                                                                          44.218

Elapsed: 00:00:00.01
ALEX@DEV > EDIT
Wrote file afiedt.buf

  1  SELECT AVG(
  2  EXTRACT(SECOND FROM TRACKING_END_TIME)
  3  -
  4  EXTRACT(SECOND FROM TRACKING_START_TIME)
  5  )
  6* FROM TIME_TRACKING
ALEX@DEV > /

AVG(EXTRACT(SECONDFROMTRACKING_END_TIME)-EXTRACT(SECONDFROMTRACKING_START_TIME))
--------------------------------------------------------------------------------
                                                                          22.109

Elapsed: 00:00:00.00
ALEX@DEV >
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19993763
Try this:

select sum(
EXTRACT(SECOND FROM TRACKING_START_TIME) -
EXTRACT(SECOND FROM TRACKING_END_TIME'))  thetime
FROM JCI_TRACKING_SUMMARY where COMPLETE_TRACK_STEP = 4;
0
 

Author Comment

by:aliasim99
ID: 19993846
Yes that is good but it eliminates Minutes I know I said it's usually 5-10 sec but it could even be in minutes or hours we need to use the full timestamp.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19993965
 1  SELECT AVG(
  2  EXTRACT(MINUTE FROM TRACKING_END_TIME)
  3  -
  4  EXTRACT(MINUTE FROM TRACKING_START_TIME)
  5  )||':'||
  6  AVG(
  7  EXTRACT(SECOND FROM TRACKING_END_TIME)
  8  -
  9  EXTRACT(SECOND FROM TRACKING_START_TIME)
 10  )
 11* FROM TIME_TRACKING
ALEX@DEV > /

AVG(EXTRACT(MINUTEFROMTRACKING_END_TIME)-EXTRACT(MINUTEFROMTRACKING_START_TIME))|
---------------------------------------------------------------------------------
20:22.109
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19993978
Yours:

select
sum(
EXTRACT(MINUTE FROM TRACKING_START_TIME) -
EXTRACT(MINUTE FROM TRACKING_END_TIME')) ||':'||
sum(
EXTRACT(SECOND FROM TRACKING_START_TIME) -
EXTRACT(SECOND FROM TRACKING_END_TIME'))  thetime
FROM JCI_TRACKING_SUMMARY where COMPLETE_TRACK_STEP = 4;
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 1800 total points
ID: 19993994
To add HOUR:

SELECT
AVG(
EXTRACT(HOUR FROM TRACKING_END_TIME)
-
EXTRACT(HOUR FROM TRACKING_START_TIME)
)||':'||
AVG(
EXTRACT(MINUTE FROM TRACKING_END_TIME)
-
EXTRACT(MINUTE FROM TRACKING_START_TIME)
)||':'||
AVG(
EXTRACT(SECOND FROM TRACKING_END_TIME)
-
EXTRACT(SECOND FROM TRACKING_START_TIME)
)
FROM TIME_TRACKING
0
 

Author Comment

by:aliasim99
ID: 19994167
Ok Here is the final Query I ran

select
AVG(
EXTRACT(HOUR FROM TRACKING_START_TIME)-
EXTRACT(HOUR FROM TRACKING_END_TIME)
)||':'||
AVG(
EXTRACT(MINUTE FROM TRACKING_START_TIME) -
EXTRACT(MINUTE FROM TRACKING_END_TIME)) ||':'||
AVG(
EXTRACT(SECOND FROM TRACKING_START_TIME) -
EXTRACT(SECOND FROM TRACKING_END_TIME))  thetime
FROM JCI_TRACKING_SUMMARY where COMPLETE_TRACK_STEP = 4;

THETIME                                                                                                                    
--------------------------------------------------------------------------------------------------------------------------
0:14:2.91                                                                                                                  

1 rows selected

Then I added another record with track step 4. Finding AVG individually may not work because you can even get negative average

TRACKING_START_TIME       TRACKING_END_TIME        
------------------------- -------------------------
21-SEP-07 08.11.07.417000000 AM 21-SEP-07 08.00.17.000000000 AM
20-SEP-07 08.48.19.910000000 AM 20-SEP-07 08.34.17.000000000 AM


For the times above.. this is the result I get

THETIME                                                                                                                    
--------------------------------------------------------------------------------------------------------------------------
0:12.5:-3.3365  
0
 

Author Comment

by:aliasim99
ID: 19994268
Well the average is fine we just need to make it positive
0
 

Author Comment

by:aliasim99
ID: 19994545
I used ABS() to make it positive
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 19995844
Glad it worked!!
0
 

Author Comment

by:aliasim99
ID: 20007583
Thanks for all your help... really appreciate it.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question