Martin AJ Martin AJ - 1 month ago 13
MySQL Question

How can I count the number of posts?

Here is my table structure:

-- reputations
+----+-------------+---------+-------+------------+------------+
| id | post_id | user_id | score | reputation | date_time |
+----+-------------+---------+-------+------------+------------+ -- Suppose:
| 1 | 1 | 1 | 1 | 5 | 1500489844 | -- out of last week
| 2 | 4 | 3 | -1 | -2 | 1500499815 | -- out of last week
| 3 | 2 | 3 | 1 | 5 | 1500584821 |
| 4 | 3 | 1 | 1 | 5 | 1501389166 |
| 5 | 2 | 4 | 1 | 5 | 1501399142 |
| 6 | 2 | 1 | -1 | -2 | 1501399142 |
| 7 | 4 | 1 | 0 | 15 | 1501481186 |
| 8 | 5 | 1 | 1 | 5 | 1501481297 |
+----+-------------+---------+-------+------------+------------+
-- Note: the last row came from an accepted-answer, that's why its score is 0

-- post_tag
+---------+--------+
| post_id | tag_id |
+---------+--------+
| 1 | 2 |
| 1 | 4 |
| 2 | 2 |
| 3 | 1 |
| 3 | 4 |
| 4 | 3 |
| 5 | 1 |
+---------+--------+

-- tags
+----+--------+
| id | name |
+----+--------+
| 1 | php |
| 2 | html |
| 3 | css |
| 4 | mysql |
+----+--------+


And here is my query:

SELECT
t.tag, sum(r.reputation) AS tag_reputation, sum(r.score) AS tag_score
FROM
users u
LEFT JOIN reputations r
ON r.user_id = u.id
AND r.date_time > 1500584821
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
u.id, u.user_name, t.tag
ORDER BY
u.id, tag_reputation DESC;


And here is the current result:

tag | tag_reputation | tag_score
----: | :------------- | :---------
css | 15 | 0
php | 10 | 2
mysql | 5 | 1
html | -2 | -1


As you can see, the result is a list of tags with reputations and scores for a specific user ordered by
tag_reputation
. Fiddle




Now I want to also count the number of posts for each tag. So this is the expected result:

tag | tag_reputation | tag_score | post_num
----: | :------------- | :--------- | :-------
css | 15 | 0 | 1
php | 10 | 2 | 2
mysql | 5 | 1 | 1
html | -2 | -1 | 1


How can I do that? I think I have to work on
post_id
column and
GROUP BY
clause. But I don't know how exactly.

Answer Source

I post this using mobile phone so I cannot try it on the fiddle you gave. I modify your SQL to count number of post using COUNT.

SELECT
t.tag, 
sum(r.reputation) AS tag_reputation, 
sum(r.score) AS tag_score,
COUNT(DISTINCT pt.post_id) AS post_num
FROM
users u
LEFT JOIN reputations r 
    ON r.user_id = u.id 
        AND r.date_time > 1500584821
JOIN post_tag pt ON pt.post_id = r.post_id
JOIN tags t ON t.id = pt.tag_id
WHERE u.id = 1 -- Specific user: Jack
GROUP BY
u.id, u.user_name, t.tag 
ORDER BY
u.id, tag_reputation DESC;

Edit: I add COUNT with DISTINCT. See if it solve.