How do I output all rows of MySQL table and include a column for counting duplicates?

Question

I have found questions related to removing duplicates and counting duplicates, but I haven't been able to add a duplicate count to MySQL rows, so I pose this question. Here's what I have so far:

$query = "SELECT * FROM Losers ORDER BY Date DESC"; 
$result = mysql_query($query) or die(mysql_error());
echo '<table>';
while($row = mysql_fetch_array($result)){
    echo '<tr><td>' . $row['Name']. "</td><td>". date("l, M j, Y",strtotime($row['Date'])) . '</td></tr>';
}
echo '</table>';
mysql_close($con);

Now I want to include a count of the duplicates in Name for each row. So, it would read something like this:

Ben 2
Laura 3
Amy 1
Laura 3
Ben 2
Laura 3

I found a this query to group duplicates in Name and count them:

$newQuery = "SELECT Name, Date, COUNT(Name) FROM Losers GROUP BY Name";

But then it will output:

Ben 2
Laura 3
Amy 1

How do I incorporate both queries?

a sad dude · Accepted Answer · 2012-12-10 02:15:22Z

4

Afaik, you can't do that without a subquery:

SELECT Losers.*, (SELECT COUNT(*) FROM Losers AS Sub WHERE Sub.Name = Losers.Name) AS `count` FROM Losers ORDER BY Date DESC

But personally, I'd rather do that with two separate queries instead.

Edit: see the two answers below, they're much better.

edited Dec 10, 2012 at 2:15

answered Feb 24, 2012 at 15:05

a sad dude

2,79020 silver badges20 bronze badges

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

3 Comments

Mikhail Over a year ago

This would get exponentially slow as a function of entries in Losers table.

tedders Over a year ago

Not to brag, but my answer or Basti's is better since the subquery will only be run once ;)

a sad dude Over a year ago

You're right. In fact I didn't even know that JOIN (SELECT...) is a valid statement. Stupid me )

Basti · Accepted Answer · 2012-02-24 15:09:04Z

4

SELECT Losers.Name, Losers.Date, L.Count
FROM Losers
JOIN (
    SELECT Name, COUNT( Name ) AS Count
    FROM Losers
    GROUP BY Name
) AS L ON Losers.Name = L.Name

answered Feb 24, 2012 at 15:09

Basti

4,0511 gold badge21 silver badges22 bronze badges

Comments

tedders · Accepted Answer · 2012-02-24 15:07:45Z

3

The easiest way is probably to take your query with the GROUP BY clause and use it as a subquery that you join to your original table.

I.E.

SELECT L.*, Loser_Count.count
FROM Losers L
JOIN (SELECT loser_id, COUNT(Name) count FROM Losers GROUP BY Name) Loser_Count ON
  L.loser_id = Loser_Count.loser_id 
ORDER BY L.date DESC

This should work, but I wouldn't be surprised if a SQL genius comes back with a more efficient way. Hope this helps!

answered Feb 24, 2012 at 15:07

tedders

1,0222 gold badges11 silver badges22 bronze badges

Comments

Mikhail · Accepted Answer · 2012-02-24 15:16:20Z

1

I would do it in two separate queries:

SELECT Name, COUNT(*) FROM Losers GROUP BY Name
/* Parse this to have a php array $l_count in form ("Ben" => 2, "Laura" => 3) */
SELECT Name FROM Losers

while ($name = mysql_fetch...) {
    print $name.' '.$l_count[$name]."\n";
}

Good luck!

answered Feb 24, 2012 at 15:16

Mikhail

9,0569 gold badges58 silver badges88 bronze badges

Comments

Churk · Accepted Answer · 2012-02-24 15:05:26Z

-1

Using SQL

SELECT field 1, field 2, field 3, COUNT(*)
FROM table
GROUP BY field 1, field 2, field 3

That SQL will give you all distinct rows, and an output of # of times they show up

answered Feb 24, 2012 at 15:05

Churk

4,6475 gold badges25 silver badges40 bronze badges

1 Comment

Basti Over a year ago

And that is exactly what he did and did not want to have.

Collectives™ on Stack Overflow

How do I output all rows of MySQL table and include a column for counting duplicates?

5 Answers 5

3 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related