MySql COUNT() function not working

Question

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!

Am I understanding correctly, that user_id 4 (username jack91) currently has 10 blog posts ? — cyadvert
– cyadvert, Commented Mar 17, 2015 at 14:19
You should be counting posts, not users. So COUNT(DISTINCT engine4_blog_blogs.blog_id ) AS Occurences (assuming that blog_id is primary key of engine4_blog_blogs) — cyadvert
– cyadvert, Commented Mar 17, 2015 at 14:22
Correct! The user has only 10 blog posts which is the greatest amount in that table for any user. — capes
– capes, Commented Mar 17, 2015 at 14:22
blog_id is primary key but its an auto-incrementing field, the owner_id is the only repeating data field. Table looks like this - blog_id, title, body, owner_type, owner_id, category_id, creation_date, modified_date — capes
– capes, Commented Mar 17, 2015 at 14:27
You JOIN by owner id. But to count, how many posts an owner has - you count unique IDs of posts. — cyadvert
– cyadvert, Commented Mar 17, 2015 at 14:36

cyadvert · Accepted Answer · 2015-03-17 14:24:48Z

1

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

answered Mar 17, 2015 at 14:24

cyadvert

8757 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2015-03-17 14:30:16Z

0

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.

edited Mar 17, 2015 at 14:30

answered Mar 17, 2015 at 14:24

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

1 Comment

capes Over a year ago

That worked perfectly, thank you so much! I knew I was forgetting something simple. Thank you again!

Collectives™ on Stack Overflow

MySql COUNT() function not working

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related