CODEWITHSUNDEEP
mysql
capes
capes

Reputation: 33

MySql COUNT() function not working

I'm trying to get the top blog poster from our mysql db

This currently works for part of it:

SELECT `username` , COUNT( `owner_id` ) AS Occurences, `owner_id`
FROM `engine4_blog_blogs` , `engine4_users`   
WHERE `engine4_users`.`user_id` = `owner_id`  
GROUP BY `owner_id`
ORDER BY Occurences DESC
LIMIT 1

which produces: 

username / Occurences / owner_id    
jack91  /    10      /      4

Which is correct.

Now what I need is to also get the persons thumbnail icon registered to them as well so I try this:

SELECT `username` , COUNT( `engine4_blog_blogs`.`owner_id` ) AS Occurences, `owner_id`, `storage_path`
FROM `engine4_blog_blogs` , `engine4_users`, `engine4_storage_files`   
WHERE `engine4_users`.`user_id` = `engine4_blog_blogs`.`owner_id` AND `engine4_users`.`user_id` = `engine4_storage_files`.`user_id`
GROUP BY `engine4_blog_blogs`.`owner_id`
ORDER BY Occurences DESC
LIMIT 1

which produces:

username /  Occurences /    owner_id /  storage_path    
jack91 /    2480 /  4 / (public/user/1f/001f_250b.JPG)

The username, owner_id & storage_path is correct but what is the db counting as this value for Occurences is not correct? I thought by specifying COUNT(engine4_blog_blogs.owner_id) it would only count that field only - also I must add that the column field owner_id only exists in engine4_blog_blogs table.

Now I have tried various permutations including JOINS, INNER JOINS and LEFT JOINS and all produce the same result... An incorrect count().

Basically I am looking for this output:

username /  Occurences /    owner_id /  storage_path    
jack91 /    10 /    4 / (public/user/1f/001f_250b.JPG)

Does anyone know what I'm doing wrong (plz keep in mind I have not touched sql in over 10 years)? Thanks for any help!

Upvotes: 2

Views: 3683

Answers (2)

Bohemian
Bohemian

Reputation: 424993

To make grouping work correctly. you must group by all non-aggregated columns:

SELECT `username` , COUNT( DISTINCT `engine4_blog_blogs`.`blog_id` ) AS Occurences, `owner_id`, `storage_path`
FROM `engine4_blog_blogs` , `engine4_users`, `engine4_storage_files`   
WHERE `engine4_users`.`user_id` = `engine4_blog_blogs`.`owner_id` AND `engine4_users`.`user_id` = `engine4_storage_files`.`user_id`
GROUP BY 1,3,4
ORDER BY Occurences DESC
LIMIT 1

You also want to add the DISTINCT keyword inside the COUNT() to get the number of differently authored blogs.*

Mysql supports non-standard grouping syntax - if you stick with the standard, it will work as you expect.

Upvotes: 0

cyadvert
cyadvert

Reputation: 875

I'll post my comment as answer.

You are counting owner_id. But should be counting his posts. So, instead of 

COUNT( `owner_id` ) AS Occurences

do 

COUNT(DISTINCT engine4_blog_blogs.blog_id)

Assuming that blog_id is primary key of engine4_blog_blogs

Upvotes: 1

Related Questions