CODEWITHSUNDEEP
sqloracle-database
banana_99
banana_99

Reputation: 641

Oracle count if column exists in other table with conditions

I have this table

COL_A   FROM    TO
------------------
  D1       1     3  
  D2       3     7

And also this other table

COL_A   VALUE
-------------
    D1      0
    D1      2
    D1      5
    D2      2
    D2      5
    D2      6

I want to obtain this. For each row in the first table, count the rows in the second table whose value is less than, between and greater than the FROM and TO columns.

COL_A   FROM    TO  LESS_THAN_FROM  BETWEEN_FROM_TO GREATER_THAN_TO
-------------------------------------------------------------------
D1         1     3               1                1               1
D2         3     7               1                2               0

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use join and conditional aggregation:

select t.col_a, t.from, t.to,
       sum(case when o.value < t.from then 1 else 0 end) as less_than,
       sum(case when o.value between t.from and t.to then 1 else 0 end) as in_between,
       sum(case when o.value > t.to then 1 else 0 end) as greater_than
from this_table t join
     other_table o
     on t.col_a = o.col_a
group by t.col_a, t.from, t.to;

Upvotes: 2

Related Questions