CODEWITHSUNDEEP
mysql
ponderingdev
ponderingdev

Reputation: 331

use COUNT(*) values from one table to another

Suppose I have two tables, users and posts. Posts has the following fields, userid, postid, etc and userid can appear multiple times as one user can write multiple posts....I'm just trying sort the users table based off the # of occurrences per userid in the posts table. I can get the # of occurrences per user using this

    SELECT userid, COUNT(*) 
    FROM posts
    GROUP BY userid;

I would like to use the values under COUNT(*) column, maybe add it to my other table because then I can simply to something like this

  SELECT * FROM users
  ORDER BY newcolumn ASC;

but I'm having trouble doing that. Or can I do it without having to add an extra column? Hints please. Thanks

Upvotes: 1

Views: 91

Answers (4)

FallAndLearn
FallAndLearn

Reputation: 4135

Left join is the key here!

SELECT users.userid,count(posts.userid) AS total_count
FROM users
LEFT JOIN posts on posts.userid = users.userid
GROUP BY users.userid
ORDER BY total_count DESC;

We are taking the left join on two tables with same user_id and we are counting the total number of posts per user using group by. Finally sort by count and show results.

Upvotes: 4

Erik
Erik

Reputation: 305

You could add a post_count column to the users table, but you would also have to update that count column every time a user creates a new post and you would have to build that logic into your application.

Otherwise, it looks like the answer from FallAndLearn will get you what you need.

Upvotes: 1

pgee70
pgee70

Reputation: 4004

try an left join:

select users.userid, [user fields],count(postid) as posts_count
from users
left join posts on posts.userid = users.userid
group by users.userid,[user fields]
order by posts_count desc.

Upvotes: 2

stevendesu
stevendesu

Reputation: 16821

You want to select users (FROM users) but you want to sort based on criteria in another table (COUNT(*) FROM posts) -- therefore you need to use a JOIN

Off-hand I can't seem to recall if "JOIN" or "RIGHT JOIN" or "FULL JOIN" is what you need if you wanted to get a cartesian product of the tables then group and aggregate on a single field, but I can avoid the need to remember with a subquery (hopefully someone will soon post a smaller and smarter answer):

SELECT users.* FROM users
JOIN (
    SELECT userid, COUNT(*) as count
    FROM posts
    GROUP BY userid
) as subquery ON users.id = subquery.userid
ORDER BY subquery.count

Note: I haven't tested this query, but it looks good to me. Again: hopefully someone will post a better answer soon as I'm not doing my due dilligence, but you definitely need a JOIN :)

Upvotes: 1

Related Questions