count from two different tables possible?

[jemminger]

is it possible to return the count of two different joins, e.g.

select name, count(posts.id) num_posts, count(friends.id) num_friendsfrom users inner join posts on posts.user_id = users.id inner join friends on friends.user_id = users.idgroup by users.id

when I try this, I get very large numbers for num_posts and num_friends, and the two numbers are identical. if I only try to select one of the counts, then i get the expected number.

thanks,
jeff

[teajay2]

you might try to doing the counts prior to doing the joins.

for instance:

select nameleft join ( select count(1), user_id from posts group by user_id ) p on u.id = p.user_idleft join ( select count(1), user_id from friends group by user_id ) f on u.id = f.user_idfrom users u

[jemminger]

fantastic… looks like that does it. thanks!