Oracle analytic functions

I recently purchased O’Reilly’s SQL cookbook. Its good. I came to know about the Oracle Window functions (analytic functions).

Some examples are
1. row_number() over()
2. dense_rank() over()
3. max() over()
4. Sum() over()
5. Lead() over()
6. Lag() over()

See here for a detailed explanation.

Check this example

Now, you have the trade_val column. But you want a running count of the same. How do you achieve it? See the query below. I have used the sum() over() function to get the sum. But hey! Its wrong. Its giving me the entire total and not the running total.

Yes, since I didnt specify anything in the over() section, it gave an entire total. So now I say order by stk_nam_key, date_key so that the running count is done as per the original order. See results below.

But hey! Again there is something different I need. I need the running count for each stk_nam_key and not for the entire table. See in the upper query, I have indicated with red arrow. When the stk_nam_key changed to 2, it gave me the running count. But I want to start fresh for every new grouping.

So what do I do? I say over(partition by stk_nam_key order by stk_nam_key,date_key). Check the results now.

Dont get confused by the first value for the new grouping 3. The date_key for both 3′s are same and hence the sum is same for both.