CODEWITHSUNDEEP
sqloracle-databaseoracle11g
BandyOrc
BandyOrc

Reputation: 159

Oracle 11g Aggregating Rows

I am trying to figure out the best way to do aggregate multiple rows together in oracle. We have a table where we log key user events as they go through a process flow. We have the following table structure:

User Id    Event Code    Event Timestamp
1            START       17/06/2013 11:00
1            END         17/06/2013 11:05
2            START       16/06/2013 11:00
2            END         16/06/2013 11:05

We are looking to get a report out of our database that will capture the timestamps for certain event codes for users in the format below:

User ID     Start Date/Time     End Date/Time
1           17/06/2013 11:00    17/06/2013 11:05
2           16/06/2013 11:00    16/06/2013 11:05

I am not sure how to do this in just SQL never mind the best way so any advice would be appreciated.

Upvotes: 1

Views: 116

Answers (4)

Randy
Randy

Reputation: 16677

if the real data is as simple as you show - then just reference the same table twice in your query - once to retrieve the start data, and once for the end date... similar to this:

select user_id, s.event_tm, as start_time, e.event_tm as end_time
from 
( select user_id, event_tm from mytable where event_cd = 'START' ) s
,( select user_id, event_tm from mytable where event_cd = 'END' ) e
where s.user_id = e.user_id

working fiddle http://www.sqlfiddle.com/#!4/d141c/7

Upvotes: 0

Mikhail Tregubov
Mikhail Tregubov

Reputation: 68

If Event Code values is consistent and differs between 'Start' and 'End' then you can just join your table on itself and filter results it like:

select t1.id as "User ID", t1."Event Timestamp" as "Start DateTime", t2."Event Timestamp" as "Start DateTime" 
from your_table t1, your_table t2 
where t1.id = t2.id and t1."Event Code" = 'START' and t2."Event Code" = 'END'

Upvotes: 0

Dibstar
Dibstar

Reputation: 2364

Assuming only one event per user:

SELECT
Userid
,MAX(CASE WHEN flag = 'START' THEN dt END) AS started
,MAX(CASE WHEN flag = 'END' THEN dt END) AS Ended
FROM
(
SELECT 1 as userid,            'START' as flag   ,    '17/06/2013 11:00' as dt FROM DUAL
UNION ALL SELECT 1,            'END' as flag    ,   '17/06/2013 11:05' As dt FROM DUAL
UNION ALL SELECT 2,            'START'     as flag , '16/06/2013 11:00' As dt FROM DUAL
UNION ALL SELECT 2,            'END'        as flag,  '16/06/2013 11:05' As dt FROM DUAL
) a
GROUP BY userid

Upvotes: 0

Ben
Ben

Reputation: 52853

Assuming that your table is how you've described this can be done with a simple PIVOT.

Given:

create table event ( id number, event_code varchar2(5), tstamp date );

insert into event values (1, 'START', to_date('17/06/2013 11:00','DD/MM/YYYY HH24:MI'));
insert into event values (1, 'END', to_date('17/06/2013 11:05','DD/MM/YYYY HH24:MI'));
insert into event values (2, 'START', to_date('16/06/2013 11:00','DD/MM/YYYY HH24:MI'));
insert into event values (2, 'END', to_date('16/06/2013 11:05','DD/MM/YYYY HH24:MI'));

The query would be:

select *
  from event
 pivot ( max(tstamp) for event_code in ('START','END') )

Here's a SQL Fiddle

Upvotes: 3

Related Questions