I have a table like below.
Id amount
--------------
10. 12345
10. 12345
12. 34567
13. 34567
As per my business requirement same id with same amount is not duplicate record. different Ids wtih same amount is duplicate record. hope you understood the requirement.
In the above sample record I have to get the duplicate amount values and its count and at the same time Id should be different.
The expected query result is 34567 and count as 2.
IF you need to display id as well,
SELECT a.*
FROM
(
SELECT id, amount, count(1) OVER (PARTITION BY amount) num_dup
FROM table1
)a
WHERE a.num_dup >1
Update. If you care only about distinct id , use COUNT(DISTINCT id) instead of COUNT(1)
More examples.
With joining another table
SELECT a.*
FROM
(
SELECT a.id, a.amount,
count(distinct a.id) OVER (PARTITION BY a.amount) num_dup
FROM table1 a
INNER JOIN table2 b ON (b.id = a.id)
)a
WHERE a.num_dup >1
Without window function and without table1.id :
SELECT a.amount, count(distinct a.id)
FROM table1 a
INNER JOIN table2 b ON (b.id = a.id)
GROUP BY a.amount
HAVING count(distinct a.id) >1 ;
Without window function and with table1.id :
SELECT b.*
FROM
(
SELECT a.amount, count(distinct a.id)
FROM table1 a
INNER JOIN table2 b ON (b.id = a.id)
GROUP BY a.amount
HAVING count(distinct a.id) >1
)a
INNER JOIN table1 b ON (b.amount = a.amount)
