MySQL count problem

Question

I've been trying to count how many times a tag has been entered into the database and display the number of times it has been entered along with the tag into the database just like here on StackOverflow but I can't seem to be able to do it can someone help me?

So far I can get the tag but not the count.

Thanks in advance for the help!

Here is my MySQL & PHP code.

$dbc = mysqli_query($mysqli,"SELECT tags.*, posts_tags.* 
                             FROM tags 
                             INNER JOIN posts_tags ON tags.id = posts_tags.tag_id
                             GROUP BY tags.tag
                             ORDER BY tags.tag ASC");

if (!$dbc) {
    print mysqli_error($mysqli);
}

while($row = mysqli_fetch_assoc($dbc)) {
    $tag = $row['tag'];

    echo '<a href="http://localhost/tags/">' . $tag . '</a>';

}

Daniel Vassallo · Accepted Answer · 2010-08-09 01:02:06Z

3

You may want to try the following:

SELECT      tags.tag, COUNT(DISTINCT posts_tags.post_id) as number_of_tags
FROM        tags 
INNER JOIN  posts_tags ON tags.id = posts_tags.tag_id
GROUP BY    tags.tag
ORDER BY    tags.tag ASC;

Test case:

CREATE TABLE tags (id int, tag varchar(10));
CREATE TABLE posts_tags (post_id int, tag_id int);

INSERT INTO tags VALUES (1, 'javascript');
INSERT INTO tags VALUES (2, 'php');
INSERT INTO tags VALUES (3, 'mysql');

INSERT INTO posts_tags VALUES (1, 1);
INSERT INTO posts_tags VALUES (2, 2);
INSERT INTO posts_tags VALUES (3, 1);
INSERT INTO posts_tags VALUES (4, 2);
INSERT INTO posts_tags VALUES (5, 3);
INSERT INTO posts_tags VALUES (6, 1);
INSERT INTO posts_tags VALUES (7, 1);
INSERT INTO posts_tags VALUES (8, 2);
INSERT INTO posts_tags VALUES (9, 2);
INSERT INTO posts_tags VALUES (10, 1);

Result:

+------------+----------------+
| tag        | number_of_tags |
+------------+----------------+
| javascript |              5 |
| mysql      |              1 |
| php        |              4 |
+------------+----------------+
3 rows in set (0.00 sec)

edited Aug 9, 2010 at 1:02

answered Aug 9, 2010 at 0:17

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

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

7 Comments

Daniel Vassallo Over a year ago

@helpME: Also check out OMG Ponies' answer, if you want to display tags where the count is 0.

helpME Over a year ago

@Daniel Vassallo but if I count the tag.id my count will always be 1 wouldn't it?

Daniel Vassallo Over a year ago

@helpME: Yes, in fact, I'm counting post_id... UPDATE: I had a typo in fact. posts_tags.id should have been posts_tags.post_id. Updated answer.

helpME Over a year ago

@Daniel Vassallo but counting the tag.id is fine isn't it?

Daniel Vassallo Over a year ago

@helpME: It is actually, if you remove the DISTINCT. But I think it feels more natural to "count the distinct posts" for each tag.

|

OMG Ponies · Accepted Answer · 2010-08-09 01:24:16Z

2

If you want a list of tags, including those with a count of zero, use a LEFT JOIN:

   SELECT t.tag, 
          COALESCE(COUNT(DISTINCT pt.post_id), 0) AS tag_count
     FROM TAGS t
LEFT JOIN POSTS_TAGS pt ON pt.tag_id = t.id
 GROUP BY t.tag
 ORDER BY t.tag

If you only want to see those that have been used one or more times, use an INNER JOIN:

  SELECT t.tag, 
         COUNT(DISTINCT pt.post_id) AS tag_count
    FROM TAGS t
    JOIN POSTS_TAGS pt ON pt.tag_id = t.id
GROUP BY t.tag
ORDER BY t.tag

edited Aug 9, 2010 at 1:24

answered Aug 9, 2010 at 0:26

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

4 Comments

Daniel Vassallo Over a year ago

Yep, I forgot to mention the LEFT JOIN :)

OMG Ponies Over a year ago

@Daniel Vassallo: Someone has to keep you on your toes :)

Daniel Vassallo Over a year ago

Why did you change the COUNT to COUNT(DISTINCT tag_id)? Wouldn't that display always 1 since you're grouping by tag?... A GROUP_CONCAT(pt.tag_id) would return something like 1,1,1,1,1.

OMG Ponies Over a year ago

@Daniel Vassallo: You're mistaken - I changed the column referenced in the COUNT(DISTINCT from pt.id to pt.tag_id because I re-read & wasn't sure pt.id existed. But you're right about the tally, and I see the column to be counted has been clarified so I'll update momentarily. Good catch!

Collectives™ on Stack Overflow

MySQL count problem

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related