CODEWITHSUNDEEP
sqloracle-database
Heisenberg
Heisenberg

Reputation: 5279

How to count the number of duplicate in sql

I have tables like following I'd like to count duplicate and assign them.

customer date       type
A       2020/8/1     a
A       2020/8/1     b
B       2019/5/1     a
B       2019/6/1     c

I'd like to count duplicate in customeranddate the, assign the number of duplicate of them.

["customer,"date"]

customer date     type     duplicate
A       2020/8/1   a       2
A       2020/8/1   b       2
B       2019/5/1   a       1
B       2019/6/1   c       1

If someone has opinion to assign the number of duplicated rows. Please let me know. thanks

Upvotes: 0

Views: 111

Answers (2)

user12766509
user12766509

Reputation:

With count() over analytical function.Date is reserved word

SELECT c.*,
       COUNT(*) OVER (PARTITION BY "customer","date") duplicate 
FROM   tablename c;

If you wanted to format date column

SELECT "customer",
        to_char("date",'DD-MON-YYYY HH:MI:SS AM') "date",
        "type",
       COUNT(*) OVER (PARTITION BY "customer","date") duplicate 
FROM tablename ;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try the below -

select a.customer,a.date,type,duplicate 
from tablename a
join 
(select customer,date, count(*) as duplicate from tablename group by customer, date) b 
on a.customer=b.customer and a.date=b.date

Upvotes: 0

Related Questions