Ad:

MySQL Discussion Thread
Marked Solved
Views: 2294

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Mar 2nd, 2009

Help with mysql query + Count

Expand Post »

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

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.

Similar Threads

Displaying date from mysql in PHP
Mysql Query in PHP
MySQL count(*) in C#
MySQL syntax error check for the right syntax to use near 'A (A) )' at line 1 in PHP
MySQL nested query / joined query conversion help in MySQL

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Mar 3rd, 2009

Re: Help with mysql query + Count

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

javmedia

Reputation Points: 22

Solved Threads: 5

Light Poster

Offline

36 posts
since Mar 2009

Permalink

Mar 3rd, 2009

Re: Help with mysql query + Count

I will. Thanks. Sub queries look interesting.

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Mar 3rd, 2009

Re: Help with mysql query + Count

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

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 4th, 2009

Re: Help with mysql query + Count

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!

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Mar 4th, 2009

Re: Help with mysql query + Count

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

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)

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;

javmedia

Reputation Points: 22

Solved Threads: 5

Light Poster

Offline

36 posts
since Mar 2009

Permalink

Mar 4th, 2009

Re: Help with mysql query + Count

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 !

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Permalink

Mar 4th, 2009

Re: Help with mysql query + Count

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.

javmedia

Reputation Points: 22

Solved Threads: 5

Light Poster

Offline

36 posts
since Mar 2009

Permalink

Mar 4th, 2009

Re: Help with mysql query + Count

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

this will work

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 4th, 2009

Re: Help with mysql query + Count

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

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.

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

Both returns the same result.

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

Last edited by nav33n; Mar 4th, 2009 at 9:43 am.

nav33n

Moderator

Featured Poster

Reputation Points: 524

Solved Threads: 356

Purple hazed!

Offline

3,878 posts
since Nov 2007

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Row locking ?

Next Thread in MySQL Forum Timeline: My rs is readOnly Item

DaniWeb Message

Cancel Changes

User Name
Password