CODEWITHSUNDEEP
sqloracle-database
cjacobso
cjacobso

Reputation: 389

Count occurrences of field values as they are displayed in order

thanks in advance for the help and sorry for how the "table" looks. Here's my question...

Let's say I have a subquery with this table (imagine the bold as column headers) as its output -

id 1 1 2 3 3 3 3 4 5 6 6 6

action o c o c c o c o o c c c

I would like my new query to output -

id 1 1 2 3 3 3 3 4 5 6 6 6

action o c o c c o c o o c c c

ct 1 2 1 1 2 3 4 1 1 1 2 3

#c 0 1 0 1 2 2 3 0 0 1 2 3

#o 1 1 1 0 0 1 1 1 1 0 0 0

where ct stands for count. Basically, I want to count (for each id) the occurrences of consecutive id and action as they happen. Let me know if this makes sense, and if not, how I can clarify my question.

Note: I realize the lag/lead functions may be helpful in this situation, along with the row_number() function. Looking for as many creative solutions as possible!

Upvotes: 0

Views: 55

Answers (2)

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

SQL> select id, action,
  2  row_number() over(partition by id order by rowid) ct,
  3  sum(decode(action,'c',1,0)) over(partition by id order by rowid) c#,
  4  sum(decode(action,'o',1,0)) over(partition by id order by rowid) o#
  5  from t1
  6  /

        ID A         CT         C#         O#                                   
---------- - ---------- ---------- ----------                                   
         1 o          1          0          1                                   
         1 c          2          1          1                                   
         2 o          1          0          1                                   
         3 c          1          1          0                                   
         3 c          2          2          0                                   
         3 o          3          2          1                                   
         3 c          4          3          1                                   
         4 o          1          0          1                                   
         5 o          1          0          1                                   
         6 c          1          1          0                                   
         6 c          2          2          0                                   
         6 c          3          3          0

P.S. Sorry Gordon, didn't see your post.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You are looking for the row_number() analytic function:

select id, action, row_number() over (partition by id order by id) as ct
from table t;

For #c and #o, you want cumulative sum:

select id, action, row_number() over (partition by id order by id) as ct,
       sum(case when action = 'c' then 1 else 0 end) over
           (partition by id order by <some column here>) as "#c",
       sum(case when action = 'c' then 1 else 0 end) over
           (partition by id order by <some column here>) as "#o"
from table t;

The one caveat is that you need a way to specify the order of the rows -- an id or date time stamp or something. SQL result sets and tables are inherently unordered, so there is no idea that one row comes before or after another.

Upvotes: 1

Related Questions