CODEWITHSUNDEEP
sqloracle-database
Grib
Grib

Reputation: 11

ORACLE conditional COUNT query

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!

Upvotes: 0

Views: 276

Answers (2)

Daniel Marcus
Daniel Marcus

Reputation: 2686

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 1

Related Questions