INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Counting occurrences inside a table

Counting occurrences inside a table

(OP)
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.



RE: Counting occurrences inside a table

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))

RE: Counting occurrences inside a table

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.

RE: Counting occurrences inside a table

(OP)
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.

RE: Counting occurrences inside a table

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close