Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

MYSQL: How to count total number of duplicate items

Consider the following case.

1. I have a table called [register] and it has a column called [name].
2. In the name column there are the following 5 values.

{657E63B5-2B59-4893-83BB-A09BC8650E5D}
{36A66546-CB58-4406-9A8C-42077286BF27}
{23B618D8-472A-4FF0-916F-014479753F93}
{846EE342-7039-11DE-9D20-806E6F6E6963}
{6DEEA2EF-2807-442B-B843-4834A42AA41B}

Open in new window

When I run the query below I get a return value of 1.

How would I modify it so that it returned 5?

(ie: The total number of repeat items)

SELECT COUNT(*) FROM  `register` 
WHERE  `name` 
LIKE '{657E63B5-2B59-4893-83BB-A09BC8650E5D}' 
OR '{36A66546-CB58-4406-9A8C-42077286BF27}' 
OR '{23B618D8-472A-4FF0-916F-014479753F93}' 
OR '{846EE342-7039-11DE-9D20-806E6F6E6963}' 
OR '{6DEEA2EF-2807-442B-B843-4834A42AA41B}'

Open in new window

0
AdrianSmithUK
Asked:
AdrianSmithUK
  • 12
  • 9
2 Solutions
 
SlimshaneeyCommented:
SELECT name, COUNT(*) FROM  `register`
WHERE  `name`
LIKE '{657E63B5-2B59-4893-83BB-A09BC8650E5D}'
OR '{36A66546-CB58-4406-9A8C-42077286BF27}'
OR '{23B618D8-472A-4FF0-916F-014479753F93}'
OR '{846EE342-7039-11DE-9D20-806E6F6E6963}'
OR '{6DEEA2EF-2807-442B-B843-4834A42AA41B}'
Group by name
Having count(name) > 1
0
 
AdrianSmithUKAuthor Commented:
Hi Slimshaneey

I'm sure we are nearly there but I ran the query and it returned zero.

Cheers,
Adrian
0
 
AdrianSmithUKAuthor Commented:
Just a minute.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
SlimshaneeyCommented:
Which would suggest you dont have any duplicates for those 5 values.

SELECT name, COUNT(*) FROM  `register`
Group by name
Having count(name) > 1

This query will tell you what dupes there are, and how many dupes
0
 
AdrianSmithUKAuthor Commented:
Thanks for the speedy response.

I'm actually looking for the total count of the number of times the value occurs in the table. Not the number of duplicates in the table.

For example.

name
-----------
Mick
Steve
John

Would return a value of 3 if the query was something like.

//Something
LIKE 'Mick' 
OR 'Steve' 
OR 'John' 
//Something

Open in new window


I don't know the final solution but I'm sure it should include:

Having count(name) > 0

Open in new window

0
 
AdrianSmithUKAuthor Commented:
PS: Here is a screenshot of last query.

ScreenShot
0
 
AdrianSmithUKAuthor Commented:
PPS: Notice I changed the last line.
0
 
SlimshaneeyCommented:
Try this:
Select sum(counter) from (
SELECT name, COUNT(*)  as counter FROM  `register`
WHERE  `name`
LIKE '{657E63B5-2B59-4893-83BB-A09BC8650E5D}'
OR '{36A66546-CB58-4406-9A8C-42077286BF27}'
OR '{23B618D8-472A-4FF0-916F-014479753F93}'
OR '{846EE342-7039-11DE-9D20-806E6F6E6963}'
OR '{6DEEA2EF-2807-442B-B843-4834A42AA41B}'
Group by name
Having count(name) > 0 )
0
 
AdrianSmithUKAuthor Commented:
I'm afraid that threw an error:

SELECT SUM( counter ) 
FROM (


SELECT name, COUNT( * ) AS counter
FROM  `register` 
WHERE  `name` LIKE  '{657E63B5-2B59-4893-83BB-A09BC8650E5D}'
OR  '{36A66546-CB58-4406-9A8C-42077286BF27}'
OR  '{23B618D8-472A-4FF0-916F-014479753F93}'
OR  '{846EE342-7039-11DE-9D20-806E6F6E6963}'
OR  '{6DEEA2EF-2807-442B-B843-4834A42AA41B}'
GROUP BY name
HAVING COUNT( name ) >0
) 

MySQL said: 

#1248 - Every derived table must have its own alias 

Open in new window

0
 
SlimshaneeyCommented:
Oops, after final closing brackets just add  drv1 to end,  like this:

Select sum(counter) from (
SELECT name, COUNT(*)  as counter FROM  `register`
WHERE  `name`
LIKE '{657E63B5-2B59-4893-83BB-A09BC8650E5D}'
OR '{36A66546-CB58-4406-9A8C-42077286BF27}'
OR '{23B618D8-472A-4FF0-916F-014479753F93}'
OR '{846EE342-7039-11DE-9D20-806E6F6E6963}'
OR '{6DEEA2EF-2807-442B-B843-4834A42AA41B}'
Group by name
Having count(name) > 0 ) drv1
0
 
SlimshaneeyCommented:
It doesn't matter what goes at the end, we just need to call the sub query something, in this case drv1
0
 
AdrianSmithUKAuthor Commented:
We're getting there.

It works but returns an undesirable result.

Capture
0
 
AdrianSmithUKAuthor Commented:
Sorry Slimshaneey, I've got to leave to go home and so I'll be offline for a few hours.

Here is the sql for the table and data if it helps.

I'll log back on at 8pm.

Many thanks for your help.


CREATE TABLE `register` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(256) default '0',
  `display_name` varchar(256) NOT NULL,
  PRIMARY KEY  (`id`)
) ;


INSERT INTO `register` VALUES(31, '{6DEEA2EF-2807-442B-B843-4834A42AA41B}', 'Teredo Tunneling Pseudo-Interface');
INSERT INTO `register` VALUES(30, '{846EE342-7039-11DE-9D20-806E6F6E6963}', 'Loopback Pseudo-Interface 1');
INSERT INTO `register` VALUES(29, '{23B618D8-472A-4FF0-916F-014479753F93}', 'Local Area Connection');
INSERT INTO `register` VALUES(28, '{36A66546-CB58-4406-9A8C-42077286BF27}', 'Wireless Network Connection');
INSERT INTO `register` VALUES(27, '{657E63B5-2B59-4893-83BB-A09BC8650E5D}', 'Local Area Connection 3');

Open in new window

0
 
SlimshaneeyCommented:
What that suggests is that there is only one of those five record names in there... The query should be working fine. Have you run each name in a separate query to ensure they all exist?
0
 
SlimshaneeyCommented:
Ah, it might be better to try this way then:

Select count( distinct name) from register where name in ( '{657E63B5-2B59-4893-83BB-A09BC8650E5D}'
,'{36A66546-CB58-4406-9A8C-42077286BF27}'
, '{23B618D8-472A-4FF0-916F-014479753F93}'
,'{846EE342-7039-11DE-9D20-806E6F6E6963}'
,'{6DEEA2EF-2807-442B-B843-4834A42AA41B}')
0
 
AdrianSmithUKAuthor Commented:
Hi Slim

I tested the above and it works in the first scenario but fails in the case below.

The result in this situation should be 20.

(I just duplicated the 5 records 3 more times so that there are 20 in total)

CREATE TABLE `register` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(256) default '0',
  `display_name` varchar(256) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `register` VALUES(32, '{657E63B5-2B59-4893-83BB-A09BC8650E5D}', 'Local Area Connection 3');
INSERT INTO `register` VALUES(33, '{36A66546-CB58-4406-9A8C-42077286BF27}', 'Wireless Network Connection');
INSERT INTO `register` VALUES(35, '{846EE342-7039-11DE-9D20-806E6F6E6963}', 'Loopback Pseudo-Interface 1');
INSERT INTO `register` VALUES(34, '{23B618D8-472A-4FF0-916F-014479753F93}', 'Local Area Connection');
INSERT INTO `register` VALUES(36, '{6DEEA2EF-2807-442B-B843-4834A42AA41B}', 'Teredo Tunneling Pseudo-Interface');
INSERT INTO `register` VALUES(31, '{6DEEA2EF-2807-442B-B843-4834A42AA41B}', 'Teredo Tunneling Pseudo-Interface');
INSERT INTO `register` VALUES(30, '{846EE342-7039-11DE-9D20-806E6F6E6963}', 'Loopback Pseudo-Interface 1');
INSERT INTO `register` VALUES(29, '{23B618D8-472A-4FF0-916F-014479753F93}', 'Local Area Connection');
INSERT INTO `register` VALUES(28, '{36A66546-CB58-4406-9A8C-42077286BF27}', 'Wireless Network Connection');
INSERT INTO `register` VALUES(27, '{657E63B5-2B59-4893-83BB-A09BC8650E5D}', 'Local Area Connection 3');
INSERT INTO `register` VALUES(37, '{6DEEA2EF-2807-442B-B843-4834A42AA41B}', 'Teredo Tunneling Pseudo-Interface');
INSERT INTO `register` VALUES(38, '{846EE342-7039-11DE-9D20-806E6F6E6963}', 'Loopback Pseudo-Interface 1');
INSERT INTO `register` VALUES(39, '{23B618D8-472A-4FF0-916F-014479753F93}', 'Local Area Connection');
INSERT INTO `register` VALUES(40, '{36A66546-CB58-4406-9A8C-42077286BF27}', 'Wireless Network Connection');
INSERT INTO `register` VALUES(41, '{657E63B5-2B59-4893-83BB-A09BC8650E5D}', 'Local Area Connection 3');
INSERT INTO `register` VALUES(42, '{6DEEA2EF-2807-442B-B843-4834A42AA41B}', 'Teredo Tunneling Pseudo-Interface');
INSERT INTO `register` VALUES(43, '{846EE342-7039-11DE-9D20-806E6F6E6963}', 'Loopback Pseudo-Interface 1');
INSERT INTO `register` VALUES(44, '{23B618D8-472A-4FF0-916F-014479753F93}', 'Local Area Connection');
INSERT INTO `register` VALUES(45, '{36A66546-CB58-4406-9A8C-42077286BF27}', 'Wireless Network Connection');
INSERT INTO `register` VALUES(46, '{657E63B5-2B59-4893-83BB-A09BC8650E5D}', 'Local Area Connection 3');

Open in new window

0
 
AdrianSmithUKAuthor Commented:
Hi Slim

I removed the distinct directive and it works perfectly.

You're a star. Many thanks,
Adrian


SELECT COUNT( name ) 
FROM lwf_software_mac_address_register
WHERE name
IN (
 '{657E63B5-2B59-4893-83BB-A09BC8650E5D}',  '{36A66546-CB58-4406-9A8C-42077286BF27}',  '{23B618D8-472A-4FF0-916F-014479753F93}',  '{846EE342-7039-11DE-9D20-806E6F6E6963}', '{6DEEA2EF-2807-442B-B843-4834A42AA41B}'
)

Open in new window

0
 
SlimshaneeyCommented:
Glad you got it sorted, wasnt quite sure what you were looking for but we were there abouts :-)
0
 
SlimshaneeyCommented:
Not sure why you have asked for the question to be deleted rather than just award the points?
0
 
AdrianSmithUKAuthor Commented:
Hi Slim

I didn't ask for the question to be deleted. I selected two solutions and it won't allow me to close the question without the moderator reviewing it.

I awarded you 500 points and the correct final solution (my last comment) 0 points.

If you look at the close request it says:

"The final solution should have the DISTINCT command removed. All of the points should be awarded to  Slimshaneey"

Is that OK?
0
 
awking00Commented:
FYI, your first query would have worked except you needed to add the "OR `name` LIKE "
condition to your where clause
WHERE  `name` LIKE '{657E63B5-2B59-4893-83BB-A09BC8650E5D}'
OR `name` LIKE '{36A66546-CB58-4406-9A8C-42077286BF27}'
OR `name` LIKE '{23B618D8-472A-4FF0-916F-014479753F93}'
OR `name` LIKE '{846EE342-7039-11DE-9D20-806E6F6E6963}'
OR `name` LIKE '{6DEEA2EF-2807-442B-B843-4834A42AA41B}
0
 
AdrianSmithUKAuthor Commented:
The final solution should have the DISTINCT command removed. All ot the points should be awarded to  Slimshaneey
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now