-1

I have a table that lists activities and their durations in the order they are carried out i.e. the table is sorted by time. It has mainly four columns:

person_ID, duration, activity_ID, time

How can I sum duration up until a certain activity_ID is reached? The activities described by activity_ID could come in any order.

The table could for example list your activities during the day, such as getting up, shower, shave, eat, get dressed, put on shoes. The order and activities varies between persons and days.

I'd like to sum up the duration of all activities until a certain activity for example 'get dressed' and then stop. How do I do this in sql?

user1453235
  • 51
  • 7
  • no sorting on the activity_ID? – Matten Jul 23 '12 at 11:48
  • 2
    Ordering isn't guaranteed unless you specifically order a result set. Just because you inserted record a before record b doesn't mean that when you're querying/manipulating the data/table record a will always return "prior" to record b (unless specifically requested). – Derek Jul 23 '12 at 11:53
  • 3
    Why [the answer to your last question](http://stackoverflow.com/questions/11012924/summing-a-column-up-to-a-certain-row-using-group-by-and-over) did not work for you? – Nikola Markovinović Jul 23 '12 at 12:10
  • The table is sorted acc to when the activities took place. No sorting order on activity_ID. The answer to my prev question is as far as i can see not applicable to this question. – user1453235 Jul 23 '12 at 12:57

2 Answers2

1

Not sure if this is what you are looking for, but a general technique: turn a flag on or off for a row depending on whether you want to include it or not, then sum all the records with the flag on.

create table activity( person_ID number, duration number, activity_ID number)

insert into activity values(1, 1, 1);
insert into activity values(1, 2, 2);
insert into activity values(1, 3, 3);
insert into activity values(1, 4, 4);
insert into activity values(1, 5, 5);

insert into activity values(2, 10, 1);
insert into activity values(2, 20, 2);
insert into activity values(2, 30, 3);
insert into activity values(2, 40, 4);
insert into activity values(2, 50, 5);

SELECT person_id, SUM( CASE WHEN activity_id <= 3 THEN duration ELSE 0 END ) AS sum_till_3
  FROM activity
  GROUP BY person_id
  ORDER BY person_id

PERSON_ID SUM_TILL_3
--------- ----------
        1          6 
        2         60
Glenn
  • 8,932
  • 2
  • 41
  • 54
  • Thanks for your answer, but it won't work because you assume that activity_id is an ordered set which it isn't in my example. I want to sum duration until a certain activity_id appears. Or, put in another way, the table is sorted by another column than activity_id. – user1453235 Jul 23 '12 at 13:05
0

Here is how you can do it with regular window functions:

select t.user_id, sum(time <= activity_time then duration else 0 end)
from (select t.*,
             min(activitytime) over (partition by user_id) as activitytime
      from (select t.*,
                   (case when activity_id = xxx then 1 else 0 end) as isactivity,
                   (case when activity_id = xxx then time end) as activitytime,
            from t
           ) t
      ) t
group by user_id

This just finds the minimum time when the activity occurred for each user and then adds up the durations for them.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • A beautiful example. After a few modifications the code looks like below and works perfectly! SELECT rt.user_id, Sum(CASE WHEN rt.time < rt.activity_time THEN rt.duration ELSE 0 END) AS minutes_to_activity_xxx FROM ( SELECT t.*, Min(activity_time1) OVER (PARTITION BY user_id) as activity_time FROM ( SELECT sbm.*, (CASE WHEN sbm.moment = xxx THEN sbm.time END) AS activity_time1 FROM sbm ) t )rt GROUP BY rt.user_id – user1453235 Jul 30 '12 at 09:30