'How to count duplication items?

Every customer should not have duplicated code, as you can see the result below for example Customer-A have duplicated Code of 22 and Customer-D have duplicated Code of 44

I like to run a query to get a number of how many duplications do we have, from the result below it should be 4. I have tried using Group By Code and Having but not having much luck.

customer         Code
------        ---------
  A               11
  A               22
  A               22
  B               33
  C               22
  D               44
  D               44
  D               44
  D               22
mysql


Solution 1:[1]

We can use group by and keep the combinations with more than one line

create table t(
customer  char(1),
Code int);
insert into t values
  ('A',               11),
  ('A',               22),
  ('A',               22),
  ('B',               33),
  ('C',               22),
  ('D',               44),
  ('D',               44),
  ('D',               44),
  ('D',               22);

SELECT
  customer,
  code,
  count(*) "number"
FROM t
GROUP BY 
  customer,
  code
HAVING 
  COUNT(*) > 1;

customer | code | number
:------- | ---: | -----:
A        |   22 |      2
D        |   44 |      3

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions