Counting occurrences inside a table 1

[fayevalentine]

Hi

I have a little problem with this query, I want to know if it is the first time something happen, so I use OVER instruction, but inside my application I could insert a date range and my query just count the data selected and I have to count all table:

example
date event result (what I need)
01-09-2015 something 1
02-09-2015 other 1
10-09-2015 something 2
11-09-2015 something 3

so if I select range date 10-09-2015 to 11-09-2015 i need to return
date event result
10-09-2015 something 2
11-09-2015 something 3

but my query gets:
date event result
10-09-2015 something 1
11-09-2015 something 2

which is incorrect.

Here my query very simplified because I make several joins:

select something, sum(count(something)) over (order by trunc(date)) as running_total
from my_table
where date > selected_date
and date < selected_date_2

So Im still unable to make my query count all registers because of where condition, could anyone give me a hint?


Thanks in advance.

 

[cmmrfrds]

I can't tell exactly what you are looking for, but it looks like the size of your Window is the whole data set. Is this what you want? over (order by trunc(date))
Perhaps you want the Window size to be the something. over(partition by something order by trunc(date))

 

[taupirho]

You need a select from ( select .... construct


So lets say
select date, something event, sum(count(something)) over (order by trunc(date)) as running_total
from my_table

returns

date event result
01-09-2015 something 1
02-09-2015 other 1
10-09-2015 something 2
11-09-2015 something 3

Now,

select date,event, running_total
from
(
select date,something event, sum(count(something)) over (order by trunc(date)) as running_total
from my_table
)
where date > selected_date
and date < selected_date_2

will return

10-09-2015 something 2
11-09-2015 something 3


In order to understand recursion, you must first understand recursion.

 

[fayevalentine]

Thank you very much, yes, I did what taupirho suggested. It was a very simple solution but I was stucked and didn't see it.

 

[Andrzejek]

If you find the post helpful, please click on “Great Post? Star it” link to give a star for the help. That action let others know the problem has a solution.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.