CODEWITHSUNDEEP
sqloracle-database
lizard12012
lizard12012

Reputation: 23

Oracle Sql Counting Rows in a Table based on duplicates in another table

I have two tables in Oracle SQL Developer that are joined by a key ID+state. The first (t1) has 4 columns:

ID    measurement   Value   state
123   Nitrogen      .7      VA
123   Oxygen        .1      VA 
456   Nitrogen      .6      MI 
456   Carbon        .5      MI
456   Oxygen        .3      MI

The second table (t2) has 3 columns:

ID    date      state
123   5/20/20   VA
123   8/3/16    VA

I need to write a query that first searches the t2 for duplicate IDs+state, and then searches the second table for instances of that duplicate ID+state having fewer than three rows. The return would be the count of IDs in t2 that are duplicates and have fewer than three rows in t1 for each state in t2. In this example:

VA - 1
MI - 0

Is there an easy way to do this in Oracle? I have tried iterations of the following SQL and am having trouble finding the correct syntax, and am wondering if there is a better way. I have tried multiple iterations of the below query:

select a.ID, a.state count() totalcount from t2 A left join t1 B on a.ID=b.ID and a.state=b.state where b.ID having count < 3 group by a.id having count () >1

Thank you!

Upvotes: 0

Views: 448

Answers (1)

forpas
forpas

Reputation: 164099

Aggregate separately in eah table under your conditions and then join to aggregate for the number of distinct ids:

select t1.state, count(distinct t2.id) counter
from (
  select id, state from t1
  group by id, state
  having count(*) > 1
) t1 left join (
  select id, state from t2
  group by id, state
  having count(*) < 3
) t2
on t2.id = t1.id and t2.state = t1.state
group by t1.state

Or with COUNT() window function:

select t1."state", count(distinct t2.id) counter
from (
  select id, "state", 
         count(*) over (partition by id, "state") counter1
  from t1
) t1 left join (
  select id, "state", 
         count(*) over (partition by id, "state") counter2
  from t2
) t2
on t2.id = t1.id and t2."state" = t1."state"
and t1.counter1 > 1 and t2.counter2 < 3
group by t1."state"

See the demo.
Results:

> state | COUNTER
> :---- | ------:
> VA    |       1
> MI    |       0

Upvotes: 1

Related Questions