Dreamer Dreamer - 2 months ago 11
SQL Question

one query to add a field for duplicate values in a column (oracle)

Assume a table:

--------------------------------
Col A | Col B | Col C | Col D
--------------------------------
xx xxx xxxx 2000
xx xxx xxxx 3000
xx xxx xxxx 2000
xx xxx xxxx 1450
xx xxx xxxx 1000
xx xxx xxxx 2000
xx xxx xxxx 1000


I want to have a query to have a column of value that presents whether if there a value in col D has the same value in the column, so that

-------------------------------------
Col A | Col B | Col C | Col D | Col E
-------------------------------------
xx xxx xxxx 2000 Y
xx xxx xxxx 3000 N
xx xxx xxxx 2000 Y
xx xxx xxxx 1450 N
xx xxx xxxx 1000 Y
xx xxx xxxx 2000 Y
xx xxx xxxx 1000 Y


It will be even better to have the column shows how many duplicates

-------------------------------------
Col A | Col B | Col C | Col D | Col E
-------------------------------------
xx xxx xxxx 2000 3
xx xxx xxxx 3000 1
xx xxx xxxx 2000 3
xx xxx xxxx 1450 1
xx xxx xxxx 1000 2
xx xxx xxxx 2000 3
xx xxx xxxx 1000 2

Answer

You just want count(*) as a window function:

select t.*,
       count(*) over (partition by d) as e
from t;