Solved

MYSQL: How to count total number of duplicate items

Posted on 2013-01-17
22
522 Views
Last Modified: 2013-01-22
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
Comment
Question by:AdrianSmithUK
  • 12
  • 9
22 Comments
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38787980
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
 

Author Comment

by:AdrianSmithUK
ID: 38788047
Hi Slimshaneey

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

Cheers,
Adrian
0
 

Author Comment

by:AdrianSmithUK
ID: 38788060
Just a minute.
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38788069
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
 

Author Comment

by:AdrianSmithUK
ID: 38788131
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
 

Author Comment

by:AdrianSmithUK
ID: 38788146
PS: Here is a screenshot of last query.

ScreenShot
0
 

Author Comment

by:AdrianSmithUK
ID: 38788154
PPS: Notice I changed the last line.
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38788184
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
 

Author Comment

by:AdrianSmithUK
ID: 38788195
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
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38788220
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
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38788247
It doesn't matter what goes at the end, we just need to call the sub query something, in this case drv1
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:AdrianSmithUK
ID: 38788251
We're getting there.

It works but returns an undesirable result.

Capture
0
 

Author Comment

by:AdrianSmithUK
ID: 38788285
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
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38788306
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
 
LVL 11

Assisted Solution

by:Slimshaneey
Slimshaneey earned 500 total points
ID: 38788321
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
 

Author Comment

by:AdrianSmithUK
ID: 38789314
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
 

Accepted Solution

by:
AdrianSmithUK earned 0 total points
ID: 38789347
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
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38789371
Glad you got it sorted, wasnt quite sure what you were looking for but we were there abouts :-)
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 38789457
Not sure why you have asked for the question to be deleted rather than just award the points?
0
 

Author Comment

by:AdrianSmithUK
ID: 38789585
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
 
LVL 31

Expert Comment

by:awking00
ID: 38801954
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
 

Author Closing Comment

by:AdrianSmithUK
ID: 38804818
The final solution should have the DISTINCT command removed. All ot the points should be awarded to  Slimshaneey
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article discusses four methods for overlaying images in a container on a web page
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now