How to count the number of duplicate in sql

Question

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

score 1 · Accepted Answer · 2020-08-20 04:35:54Z

1

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 ;

edited Aug 20, 2020 at 4:35

answered Aug 20, 2020 at 4:24

user12766509

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2020-08-20 03:44:55Z

0

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

answered Aug 20, 2020 at 3:44

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to count the number of duplicate in sql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related