SEO

hey all,

I have my query that counts posts per user:

SELECT count(*) as counted, c.user_id FROM posts c group by c.user_id
having counted>1 order by counted DESC LIMIT 20

I wanted to add user login for each count so I did:

SELECT count(*) as counted, u.login FROM posts c left join users u on
posts.poster_id=u.id group by c.user_id having counted>1 order by
counted DESC LIMIT 20

but now I get more results.

Any idea what I'm doing wrong?

Thanks in advance

Pat

On Sat, May 3, 2008 at 6:00 PM, Patrick Aljord <patcito@gmail.com> wrote:
> SELECT count(*) as counted,
> u.login
> FROM posts c
> left join users u on posts.poster_id=u.id
> group by c.user_id
> having counted>1
> order by counted DESC
> LIMIT 20

This is a bad query. You are abusing GROUP BY.

Only use GROUP BY if if your SELECT clause is going to return the
results of an aggregating functions or what you are grouping by. If
you need more info throw the GROUP BYed query in a derived table. What
you are doing is an illegal query in most databases, and will return
random results.

Your doing a left join which can increase the number of rows returned.
This is then GROUP BYed and run through a HAVING. Is:
posts.poster_id=users.id
a one to one relationship? If it is not, then count(*) would be a
larger number and pass the HAVING. This may not be your problem, but I
suggest you have more than you realize.

I suggest reading
http://www.xaprb.com/blog/2006/04/26...ing-standards/ (because
what you posted violated several of the suggestions of that very good
post, and was therefore less easy to read) and the post it links to on
'how to group data correctly in SQL'. I have a good idea on how to
solve your problem using your current syntax (because for a while I
worked without anyone telling me that what I was doing was wrong), but
I am not going to tell you because you should be writing valid SQL.

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

> Your doing a left join which can increase the number of rows returned.
> This is then GROUP BYed and run through a HAVING. Is:
> posts.poster_id=users.id
> a one to one relationship? If it is not, then count(*) would be a
> larger number and pass the HAVING. This may not be your problem, but I
> suggest you have more than you realize.

this is not a one-to-one as a user can make many comments but a
comment belongs to one user only, so I guess the left join returns
more than necesary. but I'm kind of stuck on that one :/

> I suggest reading
> http://www.xaprb.com/blog/2006/04/26...ing-standards/

I read it thanks but I still can't find a way to do it right, I mean I
am following this pattern right no?:

select column ...
from table ...
where criterion ...
group by groupingclause ...
having havingclause ...
order by orderingclause ...

Thanks in advance

Pat

On Sun, May 4, 2008 at 5:37 PM, Patrick Aljord <patcito@gmail.com> wrote:
> > Your doing a left join which can increase the number of rows returned.
> > This is then GROUP BYed and run through a HAVING. Is:
> > posts.poster_id=users.id
> > a one to one relationship? If it is not, then count(*) would be a
> > larger number and pass the HAVING. This may not be your problem, but I
> > suggest you have more than you realize.
>
> this is not a one-to-one as a user can make many comments but a
> comment belongs to one user only, so I guess the left join returns
> more than necesary. but I'm kind of stuck on that one :/
I said one to one, meant many to one (good) but not many to many (bad
for this query). Sorry.Your join on something different than what you
are grouping on. If you are getting more results look at what where
the row count increases. If the only thing that changed was the join
then you are getting more results because of it. I would investigate
the table you are joining against.

> > I suggest reading
> > http://www.xaprb.com/blog/2006/04/26...ing-standards/
>
> I read it thanks but I still can't find a way to do it right, I mean I
> am following this pattern right no?:
What came over was:

SELECT count(*) as counted, u.login FROM posts c left join users u on
posts.poster_id=u.id group by c.user_id having counted>1 order by
counted DESC LIMIT 20

I also suggested reading a blog that he linked to about how to use
GROUP BY. So a search for 'how to group data correctly in SQL'.

>
> select column ...
> from table ...
> where criterion ...
> group by groupingclause ...
> having havingclause ...
> order by orderingclause ...
>
> Thanks in advance
>
> Pat
>



--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

If you are getting more results, I would guess that your users table
is not a list of unique users, but a list of user logins. If that is
the case, then it's your LEFT JOIN and the count(*) that is causing
you to get more results.
If a user logs in 5 times, but only has 1 post, you will get 5 records
for that user out of the LEFT JOIN. That's the way left joins work.
You're then doing a count on all the records, but you really just want
a count of the number of posts.
If all my assumptions are correct, then a quick fix for your query is
to change your count(*) to this:
count(DISTINCT posts.post_id) as counted

That will count the number of unique posts. I don't know what your
unique field name is for the posts table.

Brent Baisley
Systems Architect

On Sat, May 3, 2008 at 9:00 PM, Patrick Aljord <patcito@gmail.com> wrote:
> hey all,
>
> I have my query that counts posts per user:
>
> SELECT count(*) as counted, c.user_id FROM posts c group by c.user_id
> having counted>1 order by counted DESC LIMIT 20
>
> I wanted to add user login for each count so I did:
>
> SELECT count(*) as counted, u.login FROM posts c left join users u on
> posts.poster_id=u.id group by c.user_id having counted>1 order by
> counted DESC LIMIT 20
>
> but now I get more results.
>
> Any idea what I'm doing wrong?
>
> Thanks in advance
>
> Pat
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@gmail.com
>
>