CODEWITHSUNDEEP
sqloracle-database
Netfoxf
Netfoxf

Reputation: 23

How to find different values of duplicate values in the same table from two columns of that table

I have a table of products where the same product code have various different values and some have matching values. I want to get the result for the products which have various different values.

I tried using distinct it works but it also gives me the unique values

Ex : 

Prod      |      Amount 
1100005          10
1100005          15
1100005          25
1100001          10
1100001          10
1100001          10
1100002          9 
1100002          8
1100002          7
1100003          54
1100003          54

I want the result as follows : -

Prod      |      Amount 
1100005          10
1100005          15
1100005          25
1100002          9 
1100002          8
1100002          7

Upvotes: 1

Views: 73

Answers (5)

MT0
MT0

Reputation: 167972

You can also use analytic functions to do it in a single table scan:

Oracle Setup:

CREATE TABLE table_name ( Prod, Amount ) AS 
SELECT 1100005, 10 FROM DUAL UNION ALL
SELECT 1100005, 15 FROM DUAL UNION ALL
SELECT 1100005, 25 FROM DUAL UNION ALL
SELECT 1100001, 10 FROM DUAL UNION ALL
SELECT 1100001, 10 FROM DUAL UNION ALL
SELECT 1100001, 10 FROM DUAL UNION ALL
SELECT 1100002,  9 FROM DUAL UNION ALL
SELECT 1100002,  8 FROM DUAL UNION ALL
SELECT 1100002,  7 FROM DUAL UNION ALL
SELECT 1100003, 54 FROM DUAL UNION ALL
SELECT 1100003, 54 FROM DUAL

Query:

SELECT prod,
       amount
FROM   (
  SELECT prod,
         amount,
         COUNT( * ) OVER ( PARTITION BY Prod, Amount ) AS num_per_amount_prod,
         COUNT( DISTINCT Amount ) OVER ( PARTITION BY Prod ) AS num_amount_per_prod 
  FROM   table_name
)
WHERE  num_amount_per_prod > 1
AND    num_per_amount_prod = 1

Output:

   PROD | AMOUNT
------: | -----:
1100002 |      7
1100002 |      8
1100002 |      9
1100005 |     10
1100005 |     15
1100005 |     25

db<>fiddle here

Upvotes: 0

forpas
forpas

Reputation: 164089

This is very simple with EXISTS:

select t.* from tablename t
where exists (
  select 1 from tablename
  where prod = t.prod and amount <> t.amount
)

See the demo.
Results:

>    PROD | AMOUNT
> ------: | -----:
> 1100005 |     25
> 1100005 |     15
> 1100005 |     10
> 1100002 |      7
> 1100002 |      8
> 1100002 |      9

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

How about just using aggregation?

select prod, amount
from t
group by prod, amount
having count(*) = 1;

This returns the prod/amount pairs that appear only once.

If you want prods all of whose amounts are different, then:

select prod_amount
from (select prod, amount, count(*) as cnt,
             max(count(*)) over (partition by prod) as max_cnt
      from t
      group by prod, amount
     ) pa
where max_cnt = 1;

These returns the same results on your data. It is unclear to me which you would want if a prod had some duplicates and some unique amounts.

Or alternatively, if you just want the products (and not the amounts):

select prod
from t
group by prd
having count(*) = count(distinct amount);

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

COUNT(*) and HAVING will be your friend here:

SELECT PROD, AMOUNT
  FROM SOME_TABLE
  GROUP BY PROD, AMOUNT
  HAVING COUNT(*) = 1
  ORDER BY PROD, AMOUNT

dbfiddle here

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use aggregation and exists

select  prod,amount
from table t1
group by prod,amount
having count(*)=1
and exists ( select 1 from table t2 where t1.prod=t2.prod
           having count(*)>1
            )

Upvotes: 1

Related Questions