Help with mysql query + Count

[dottomm]

Hello,

thanks in advance to anyone who can help me with this query.

What I am trying to do is to retreieve a list of all the users from the table 'users' and count how many posts the user has made.

The code below will shows a list of users that have made a post, but I would like is a list of all the users PLUS how many post they have made, even if the number is '0'.

select 
  count(*) as post_count,
   p.post_id, 
   p.user_id, 
   u.username 
from 
   posts p 
inner join 
   users u 
on 
   p.user_id = u.user_id
group by username"

Thanks again to anyone who can help me.

[javmedia]

I personally find inner joins in MySQL to be more nasty than awkward.
Consider using "sub queries" instead

It seemed to me they are faster in MySQL as well.

See MySQL Man page
11.2.8.4. Subqueries with ALL

[dottomm]

I will. Thanks. Sub queries look interesting.

[dickersonka]

you are trying to do an inner join on something that isn't there, all users don't have posts

unlike javamedia, i would suggest trying to use inner or outer joins, at least to me it makes life much easier

try this, think it should work

select count(*) as post_count,
u.user_id
from users u
left outer join
posts p
on p.user_id = u.user_id
group by u.user_id

[dottomm]

Thank you dickersonka! That is exactly what I was trying to do. I can now see what I was doing wrong with my other query.
One quirk though - In using the query you offered, I get post_count of 1 though there is none by that user. The weird part (and the part that makes it okay for what I'm doing) is it will show 1 post for =0 and it will continue to read 1 until the post_count reaches 2. Then it counts fine. does that make sense? Any idea? Again, thanks a ton!

[javmedia]

Good one, but I wouldn't settle for miscalculations being spat back at me from the system.

I was just working on a similar thing as a reference using subqueries, and this may not be pretty but very fast.

(4x12 ^ 2) * 80 entries gone through in 0.0020 seconds

SELECT `tid` , `c0` AS xc0, `c1` AS xc1, `tn` , `act` , (

SELECT `cname`
FROM `c0`
WHERE `c0` = xc0
LIMIT 1
) AS c0name, (

SELECT `cname`
FROM `c1`
WHERE `c0` = xc0
AND `c1` = xc1
LIMIT 1
) AS c1name
FROM `training`
WHERE `c0` = NULL
OR `c0` <3
ORDER BY `xc0` , `xc1` , `tid`

As you see from my own situation as an example, you should be able to do your count by 0, >0 and NULL posts without allowing for discrepencies in the resultset.

Also, you are not counting the fieldnames, so I recommend not to use count(*) as you then weed the whole set.
Only count your index fields at most to reduce server load.

Not tried this but wouldnt something like this do the same as what you need, without errors in sum total found?

(with reservation for typos -- been working for 29 hours straight)

-- find top posters

SELECT COUNT(`p`) AS `post_count`,
 (SELECT `user_id` FROM `user` WHERE `posts`.`user_id`=`u`.`user_id`) AS `ugrp`
 FROM `posts`
 GROUP BY `ugrp`
 ORDER BY `post_count` DESC;

[nav33n]

Nice queries.. But still, that wouldn't solve what OP is looking for.. The still doesn't return the count as 0, if a user doesn't have any posts. I created dummy tables to create the same scenario and I am also clueless ! :S

[javmedia]

Thats odd.
In fact it would return a NULL if the user dont have a post at all since the JOIN would also not find a NULL without specifying a WHERE clause for this fact

Always include a WHERE X <= 0 AND X = NULL

Even in MS SQL it does the same thing.

For that reason I turned the whole thing over and first select users, then call a sub query on the posts/users, and where posts = Null or 0 set to 0 in return.

Thats the theory anyway.

I'll tinkle with a better option when I find a real need for it.
My scenario may not look big but when you consider doing a inner join on a table with 83,500 blobs over 19.1 GB of data in one table, any * in there better have a good reason behind it. :sweat:

I normally get through 17 records before the server kicks me out again with anything of that type.

FYI my example uses c0, c1 and training for storing courses by main category, sub category and course program. (that should make it more readable)

[dickersonka]

sorry bout that, was a late night and didn't think twice about it

this will work

select
(select count(*) from posts p1 where p1.user_id = u.user_id) as post_count,
u.user_id
from users u
left outer join
posts p
on p.user_id = u.user_id
group by u.user_id

[nav33n]

I modified your query a little bit and I don't know why it doesn't return users who have post = 0.

SELECT COUNT(
COMMENT ) AS `post_count` , (

SELECT `user_id`
FROM `users_table` u
WHERE `comments_table`.`user_id` = `u`.`user_id`
) AS `ugrp`
FROM `comments_table`
GROUP BY `ugrp`
ORDER BY `post_count` DESC
LIMIT 0 , 30;

Another query.

SELECT u.user_id, count( * ) AS post_count
FROM users_table u, comments_table p
WHERE u.user_id = p.user_id
GROUP BY u.user_id
LIMIT 0 , 30

Both returns the same result.

Edit: @Dickersonka, Your query works like a charm! Great job! :)

[javmedia]

YAAAA... Knew it would be doable with a subquery...
I just tested this...

select `fn`,`ln`, (select count(`orderid`) from `order` where `client`.`username`=`order`.`username`) AS `xcnt` FROM `client`
 WHERE `client`.`active`='1'
 GROUP BY `username`;

It returns a count of orders per client and sets the xcnt = 0 where no orders occurred, but shows first name and last name per client
who is active in the system

[dickersonka]

look at my previous post with a subquery just for you javamedia :-)

i'm not against them at all, just meaning don't shy away from the joins, get a understanding of them and use them in combination with subqueries

[nav33n]

YAAAA... Knew it would be doable with a subquery... I just tested this...

select `fn`,`ln`, (select count(`orderid`) from `order` where `client`.`username`=`order`.`username`) AS `xcnt` FROM `client`
 WHERE `client`.`active`='1'
 GROUP BY `username`;

It returns a count of orders per client and sets the xcnt = 0 where no orders occurred, but shows first name and last name per client who is active in the system

Subqueries are even better.. :)

select `user_id`, (select count(`comment`) from `comments_table` where `comments_table`.`user_id`=`users_table`.`user_id`) AS `xcnt` FROM `users_table`
 GROUP BY `user_id`;

I edited your query and it works ! Eureka ;)

[nav33n]

@Dickersonka, I have been advised on so many occasions not to use joins. Joins are relatively slower compared to subqueries. This is what I have read.

[javmedia]

Of course dinkersonka, I dont shun the joins, just working with MySQL I would say the subquery and other alternatives seems better for the most common users.

On all the servers I have worked on they always seem to be faster aswell. Perhaps something intrinsic to MySQL as MS SQL would be lost without joins.

MySQL even took them one step further.
The whole chapter on joins is a nightmare to beginners I bet :)

So we got two solutions to one problem. Thats what I call a good days work.

I did not see your post btw, because I had my head in myPHP just to solve this at the same time you posted it.

(pfiuuh.. its been 36 hours since sleep so I'm outta here, thanks for a nice quiz/problem. It will come in handy for sure.)

[nav33n]

So we got two solutions to one problem. Thats what I call a good days work. (pfiuuh.. its been 36 hours since sleep so I'm outta here, thanks for a nice quiz/problem. It will come in handy for sure.)

Yeah.. 1 solution, good. 2 solutions, better :)

Get some sleep dude! Cheers!

[dottomm]

Thank you Dickersonka. Works like a charm!!

Thank yo to all of you for your input and most of all your expertise!

[dickersonka]

Great working together everyone

As far as speed goes, I think its more in the indexes and proper keys and relational set up and the actual query rather than using the join alone

Just a simple example, here's a link that might be of interest to you all on the speed
http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect