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/"&gt;' . $tag . '</a>';

}

Answer 1

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)

Answer 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