0

I have 3 tables:

  • users
  • posts
  • visits

Each time a post gets visited, a new visit will be inserted in the following format:

| id | post_id

My post table has:

| id | text | user_id ...

I'm trying to retrieve the TOP 5 users with the most views on their posts in total. For example:

  1. User1 -> 4
  2. User2 -> 2
SELECT count(v.id) AS count FROM visits AS v RIGHT JOIN pastes AS p ON v.post_id = p.id RIGHT JOIN users AS u ON id = p.id

But all I get is an array with "count: 12".

  • 1
    I would expect a group by to be there somewhere... – mustaccio Oct 11 at 14:15
1

nbk has answered already, I have set up your tables in a SQL fiddle, this makes it easier to play around. Consider using this tool for any more questions, because that makes it easier to talk about table structure etc.

http://sqlfiddle.com/#!9/45220f/2

To elaborate more on the question/answer already given: You need to group by a column, so that all columns sharing the same value will be grouped to a single row in the result. Then you can use aggregate functions on this group, like count. There are other aggregate functions like sum (to add numbers in a group). For more information see the documentation at https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html .

| improve this answer | |
0

As you want the total vies for one single user

But the part with the user is only a guess , because you didn't post the user table

SELECT u.name,count(*) AS 'count'
FROM visits AS v 
RIGHT JOIN pastes AS p ON v.post_id = p.id 
RIGHT JOIN users AS u ON u.id = p.user_id 
GROUP BY u.id,u.name
ORDER BY `coun`t DESC
LIMIT 5
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.