asked on

SQL sum Function in Oracle

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

D B

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

paquicuba

Can you describe your table for us?
What are the datatypes for the two tracking_%_time columns?

johnsone

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

aliasim99

ASKER

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

aliasim99

ASKER

johnsone: This is the result I get. . Can you explain this to me.

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

1 rows selected

paquicuba

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
/

paquicuba

I meant: You need TWO values to be summed

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aliasim99

ASKER

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.

aliasim99

ASKER

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.

paquicuba

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 >

paquicuba

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;

aliasim99

ASKER

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.

paquicuba

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

paquicuba

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;

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aliasim99

ASKER

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

aliasim99

ASKER

Well the average is fine we just need to make it positive

aliasim99

ASKER

I used ABS() to make it positive

paquicuba

Glad it worked!!

aliasim99

ASKER

Thanks for all your help... really appreciate it.