MySql count duplicates in column of a table

Question

I have a table named telco which has 2 columns, id(PK) and telco_prov. I want to count the duplicate records in the telco_prov column, then display the number of times it appeared.

telco table example:

id    telco_prov
1     Smart
2     Smart
3     Globe
4     Globe
5     Globe

Here is my code:

    $query = "select telco_prov, count(*) c from telco group by telco_prov having c > 1";
    $result = mysql_query($query) or die('Error: '.mysql_error ());
    $row = mysql_fetch_assoc($result);

    echo "SMART: <font color= 'red'>".$row['c']."</font>";
    echo "<br>GLOBE: <font color= 'red'>".$row['c']."</font>";

The code has no error, but it doesn't do the counting correctly.

The result from the code above:

SMART: 2
GLOBE: 2

But it should be:

SMART: 2
GLOBE: 3

I've tried adding where statement in the query but still it doesn't work.

$query = "select telco_prov, count(*) c from telco where telco_prov = 'Smart' group by telco_prov having c > 1";

Your help would be much appreciated. Thanks!

you have two rows in your resultset, so you need to perform a loop in order to fetch all the records. — mitkosoft
– mitkosoft, Commented May 16, 2016 at 14:13

mitkosoft · Accepted Answer · 2016-05-16 14:15:21Z

3

You have to loop the results. see the below code

$query = "select telco_prov, count(*) c from telco group by telco_prov having c > 1";
$result = mysql_query($query) or die('Error: '.mysql_error ());
while($row = $mysql_fetch_assoc($result)) {
    echo $row['telco_prov'].": <font color= 'red'>".$row['c']."</font>";
}

edited May 16, 2016 at 14:15

mitkosoft

5,3361 gold badge15 silver badges32 bronze badges

answered May 16, 2016 at 14:13

Ravinder Reddy

3,8791 gold badge16 silver badges24 bronze badges

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

6 Comments

mitkosoft Over a year ago

no problem, just to ensure that entire code is correct. And one suggestion - in future better suggest mysqli_* functions to be used instead of already depreciated mysql_*. this will gain you more points and is proper way to give complete solutions.

Gelly Over a year ago

@RavinderReddy I don't know what's still wrong with thecode, but still it doesn't return all of the records in my table.

Ravinder Reddy Over a year ago

@Gelly what is the output/result after using the above code?

Gelly Over a year ago

@RavinderReddy the output is incomplete. In my case, the table contains 15 records, having: Globe (4 duplicates), Globe/TM(6 duplicates), Smart(2 duplicates), Sun Cellular(2 duplicates) and Smart/TNT/Addict Mobile(1). It only displays Globe/TM: 6 Smart: 2 Smart/TNT/Addict Mobile: 1 Sun Cellular: 2

Ravinder Reddy Over a year ago

try this query $query = "select telco_prov, count(*) c from telco group by telco_prov having count(telco_prov) > 1";

|

Denis Alimov · Accepted Answer · 2016-05-16 14:13:27Z

0

the error is here

echo "SMART: <font color= 'red'>".$row['c']."</font>";
echo "<br>GLOBE: <font color= 'red'>".$row['c']."</font>";

this code will echo all telco_prov with their counts

while ($row = mysql_fetch_assoc($result)) {
   echo '"'.$row['telco_prod'].'"':<font color="red">'.$row['c'].'</font>';
}

answered May 16, 2016 at 14:13

Denis Alimov

2,9011 gold badge21 silver badges40 bronze badges

Comments

Arun · Accepted Answer · 2016-05-16 14:15:43Z

0

Try with

while($row = mysql_fetch_assoc($result)){
  echo strtoupper($row['telco_prov']).": <font color= 'red'>".$row['c']."</font>";
}

The code you write will give only the first row. For getting all the records, you need to loop though the record

edited May 16, 2016 at 14:15

answered May 16, 2016 at 14:07

Arun

3,7519 gold badges51 silver badges90 bronze badges

Comments

user13745692 · Accepted Answer · 2022-11-09 00:55:51Z

0

The line from Reddy/mitkosoft made my day.

I have been looking for a query to find any duplicate values in a single column in a MySQL table.

This is what I used:

$result = $conn->query("SELECT column, COUNT(*) c FROM table GROUP BY column HAVING c > 1");

Does the trick!

answered Nov 9, 2022 at 0:55

user13745692

Collectives™ on Stack Overflow

MySql count duplicates in column of a table

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related