Problem with GROUP BY

[SamiBH]

Hello,

 

I'm having problem and I really need your help

 

My Tabels:

 

Posts:                          Users:                        categories:

PostID                        UserId                        CatID

UserID                        Username                  CatName

 

 

I want to list Top poster in every category. Like:

 

cat1    user1    1000 posts

cat2      user2    900 posts

cat3      user1    1000 posts

 

so, I tried to do it  :

 

SELECT p.UserID, c.id, u.username, COUNT(PostID )
FROM Posts p
LEFT JOIN users u ON u.id = p.UserID 
LEFT JOIN categories c ON c.id = p.category
GROUP BY c.id, u.Username
ORDER BY COUNT(PostID ) DESC
LIMIT 27 

 

and the output:

 

UserID id username COUNT(PostID)

11886 45 user1               1299

11885 45 user2               756

11886 45 user1               751

11886 42 user1               491

 

 

as you can see there is 3 (cat id) repeated 

 

please if anyone can help me, I will be grateful

[fenway]

That's because you can't mix JOINs and GROUP BY that way -- and you can't rely on any non-aggregated columns, either.

[SamiBH]

That's because you can't mix JOINs and GROUP BY that way -- and you can't rely on any non-aggregated columns, either.

 

thank you for your reply

 

so what should I do?

[fenway]

Drop the joins, get the desired grouped result first, then join.