ORACLE conditional COUNT query

Question

001 | 9441 | P021948  
001 | 9442 | P021948  
001 | 9443 | P021950  
001 | 9444 | P021951  
001 | 9445 | P021952  
001 | 9446 | P021948

In the above table I am looking to COUNT the third column so long as it is outside of the second column's value by (+/- 1).

In other words, I am trying to achieve a count of 2 for P021948 because values 9441 and 9442 are within 1 of each other and record 9446 is outside of that range. My intent is to achieve a total count of 5 given these conditions.

How could I go about querying?

Any advice is greatly appreciated!

Please show exactly what your expected result set is for this example — Daniel Marcus
– Daniel Marcus, Commented Apr 17, 2018 at 18:29
@Grib . . . There are three values for "P021948". Your question doesn't make sense. What if the values were 9441, 9442, 9446, 9447, 9448 . . . what would be returned? — Gordon Linoff
– Gordon Linoff, Commented Apr 17, 2018 at 18:30
Good point...I am trying to establish a relationship between the second and third column that would allow me to count the third so long as it is not the same for a deviation of 1 from the value in column 2. Is that clear? — Grib
– Grib, Commented Apr 17, 2018 at 18:36
@GordonLinoff If the value associated with P021948 are 9441,9442,9446,9447,9448 then I am trying to return a COUNT value of 2. — Grib
– Grib, Commented Apr 17, 2018 at 18:38

Gordon Linoff · Accepted Answer · 2018-04-17 18:36:40Z

1

Hmmm, I'm thinking you want to count the "islands" that are separated by a value of more than 1. If so:

select count(*)
from (select t.*, lag(col2) over (partition by col1, col3 order by col2) as prev_col2
      from t
     ) t
where prev_col2 is null or col2 - prev_col2 > 1;

Here is a rextester illustration of the query and the result.

answered Apr 17, 2018 at 18:36

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Grib Over a year ago

Would you mind reformatting this query for me, considering the following column and table names. I am unsure what I may be missing... COLUMN 1 = C_MACHINE_NR COLUMN 2 = C_SEQUENCE_NR COLUMN 3 = C_PRODUCT TABLE = HIS.T_HISJOBS

Gordon Linoff Over a year ago

@Grib . . . Yes, I do mind. You should describe the data in the question not in the comments.

Grib Over a year ago

Understood. Thanks for your time.

Daniel Marcus · Accepted Answer · 2018-04-17 18:49:43Z

1

select column1, column3, 
sum(case when lag(column3, 1, 0) over(order by column3)=column3 or  
    lead(column3, 1, 0) over(order by column3)=column3 then 1 else 0 end)
from yourtable
        group by column1, column3

edited Apr 17, 2018 at 18:49

answered Apr 17, 2018 at 18:32

Daniel Marcus

2,6841 gold badge10 silver badges13 bronze badges

2 Comments

Grib Over a year ago

Any idea why I may be receiving a "window functions are not allowed here" error?

Daniel Marcus Over a year ago

hmm not sure...try writing to a new table first using the window logic and then summing from there

Collectives™ on Stack Overflow

ORACLE conditional COUNT query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related