CODEWITHSUNDEEP
sqldatabaseoracle-database
Gary
Gary

Reputation: 899

Find count of occurrence of a row

I have a table with duplicate rows and I need to extract these duplicate rows alone. Below is an example of the table I have:

my_table:

ID   Offer  
1     10
2     10
1     12
1     10
2     20
2     10

What I want next is to count the occurrence of the offer for each ID. i.e, my final result should be:

ID    Offer    Count
1      10       1
2      10       1
1      12       1
1      10       2
2      20       1
2      10       2

As you can see, the count should increase based on the number of times the offer shows up per ID.

I tried something like:

select id,offer,count(offer) over (partition by id);

But this just gives the total count of that particular offer for that ID and is not the result I am looking for.

Any help is much appreciated!

Upvotes: 2

Views: 28

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You could use ROW_NUMBER:

select id,offer,ROW_NUMBER() over (partition by id, offer order by rownum)
from tab

Upvotes: 2

Related Questions