• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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