MySQL

Help with mysql query + Count

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Nov 2007

Posts: 53

Reputation: dottomm is an unknown quantity at this point

Solved Threads: 0

dottomm

Offline

Junior Poster in Training

Help with mysql query + Count

Mar 2nd, 2009

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'.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 
 
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"
 

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.

•

Join Date: Mar 2009

Posts: 36

Reputation: javmedia is an unknown quantity at this point

Solved Threads: 5

javmedia

Offline

Light Poster

Re: Help with mysql query + Count

Mar 3rd, 2009

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

•

Join Date: Nov 2007

Posts: 53

Reputation: dottomm is an unknown quantity at this point

Solved Threads: 0

dottomm

Offline

Junior Poster in Training

Re: Help with mysql query + Count

Mar 3rd, 2009

I will. Thanks. Sub queries look interesting.

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Help with mysql query + Count

Mar 3rd, 2009

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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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

Custom Application & Software Development
www.houseshark.net

•

Join Date: Nov 2007

Posts: 53

Reputation: dottomm is an unknown quantity at this point

Solved Threads: 0

dottomm

Offline

Junior Poster in Training

Re: Help with mysql query + Count

Mar 4th, 2009

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!

•

Join Date: Mar 2009

Posts: 36

Reputation: javmedia is an unknown quantity at this point

Solved Threads: 5

javmedia

Offline

Light Poster

Re: Help with mysql query + Count

Mar 4th, 2009

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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 
 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`
 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)

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
-- 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;
-- 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;

•

Join Date: Nov 2007

Posts: 3,749

Reputation: nav33n is a jewel in the rough

Solved Threads: 331

nav33n

Offline

Senior Poster

Re: Help with mysql query + Count

Mar 4th, 2009

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 !

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Mar 2009

Posts: 36

Reputation: javmedia is an unknown quantity at this point

Solved Threads: 5

javmedia

Offline

Light Poster

Re: Help with mysql query + Count

Mar 4th, 2009

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.

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)

Last edited by javmedia; Mar 4th, 2009 at 9:05 am.

•

Join Date: Aug 2008

Posts: 1,160

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Help with mysql query + Count

Mar 4th, 2009

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

this will work

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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

Custom Application & Software Development
www.houseshark.net

•

Join Date: Nov 2007

Posts: 3,749

Reputation: nav33n is a jewel in the rough

Solved Threads: 331

nav33n

Offline

Senior Poster

Re: Help with mysql query + Count

#10

Mar 4th, 2009

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

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
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;
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.

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
 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
 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