Make Subquery show records

[whipper]

Ok, I have one table that lists activities of hardware. I want to sum the count between activities. Here is my table:

History:
HW | SN | DateTime | Activity | count
--------------------------------------------------
101 | 2 | 5/3/03 11:45:04 PM | 1 | 0
101 | 2 | 5/3/03 11:46:04 PM | 0 | 25
101 | 2 | 5/3/03 11:47:04 PM | 0 | 34
101 | 2 | 5/3/03 11:48:04 PM | 0 | 46
101 | 2 | 5/3/03 11:49:04 PM | 1 | 0
101 | 2 | 5/3/03 11:50:04 PM | 0 | 57
101 | 2 | 5/3/03 11:51:04 PM | 0 | 68
101 | 2 | 5/3/03 11:52:04 PM | 1 | 0
105 | 1 | 5/3/03 11:53:04 PM | 1 | 0
105 | 1 | 5/3/03 11:54:04 PM | 0 | 43
105 | 1 | 5/3/03 11:55:04 PM | 1 | 0

What I want is to add the count between teh activities of "0" so my output would be like this.

HW | SN | DateTime | Activity | count
--------------------------------------------------
101 | 2 | 5/3/03 11:45:04 PM | 1 | 0
101 | 2 | 5/3/03 11:49:04 PM | 1 | 105
101 | 2 | 5/3/03 11:52:04 PM | 1 | 125
105 | 1 | 5/3/03 11:53:04 PM | 1 | 0
105 | 1 | 5/3/03 11:55:04 PM | 1 | 43

I know that a subquery is needed I just can't figure out how to add the fields.

Thanks

 

[swampBoogie]

select hw,sn,datetime,(select sum(count) from history           
 where activity = 0
  and datetime > h.datetime
  and datetime < (select min(datetime) from history
                   where activity = 1
                     and datetime > h.datetime)
  from history h
 where activity = 1
order by datetime

 

[whipper]

swampBoogie,

Thanks, but for some reason the Sum value is not showing up. I also noticed that there is &quot" missing from the second SELECT so I placed it after the "datetime> h.datetime)"

 

[swampBoogie]

oops, you are right about the parentheses.

What do you mean by not showing up?

 

[whipper]

Ok, I got it to work it just takes a really long time with all the data that I'm using (65000 rows) This works faster when I limit the search to the HW and then even faster with SN too. Thanks for your help that is what I wanted.