CODEWITHSUNDEEP
sqloracle-database
sayhaha
sayhaha

Reputation: 789

Sql SUM within range

I have data, which looks like this. To make it simple, I will display date as 'Day' + number

Date     ID
-----------
Day1     A
Day1     B
Day1     C  
Day2     A
Day2     A
Day3     A
Day4     B
Day5     A

I would like to count how many IDs are in each date. However, each date needs to contain all the previous date counts.

Meaning, if user picks range from Day3 to Day5.

Date    Count
Day3    Day1+Day2+Day3
Day4    Day1+Day2+Day3+Day4
Day5    Day1+Day2+Day3+Day4+Day5

All I can think of is use 'UNION' to add 3 queries for each day.

Upvotes: 3

Views: 2392

Answers (2)

knagaev
knagaev

Reputation: 2957

Check this stuff out

select Dt, count(*)
from
(
 select t2.Date Dt
 from t t1, (select Date from t group by Date) t2
 where t1.Date < t2.Date
)
group by Dt

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You are looking for a cumulative sum, which Oracle does support.

Try this:

select date, sum(daycnt) over (order by date)
from (select date, count(*) as daycnt
      from t
      where date between DAY_START and DAY_END
      group by t
     ) t

Upvotes: 3

Related Questions