MySql Select, Count(*) and SubQueries in Users<>Comments relations

Question

I have a task to count the quantity of users having count of comments > X.

My SQL-query looks like this:

SELECT users.id,
       users.display_name, 
       (SELECT COUNT(*) 
          FROM cms_comments 
         WHERE cms_comments.author_id = users.id) AS comments_count 
  FROM users 
HAVING comments_count > 150;

Everything is ok, it shows all users correctly. But i need query to return the quantity of all these users with one row. I don't know how to change this query to make it produce correct data.

Solution 1

I think this is what you're looking for:

select count(*) from (
    select u.id from users u
    join cms_comments c on u.id = c.author_id
    group by u.id
    having count(*) > 150
) final

Solution 2

Use the group by clause

SELECT users.id,
       users.display_name, 
       (SELECT COUNT(*) 
          FROM cms_comments 
         WHERE cms_comments.author_id = users.id) AS comments_count 
FROM users 
GROUP BY users.id, user.display_name
HAVING comments_count > 150;

This will give you a count for each of the users.id, users.display_name having a commments_count > 150

as for your comment of getting the total number of users it's best to update your question but if you want a count of all users matching this criteria use

SELECT COUNT(*) AS TotalNumberOfUsersMatchingCritera
FROM
(
    SELECT users.id,
           users.display_name, 
           (SELECT COUNT(*) 
              FROM cms_comments 
             WHERE cms_comments.author_id = users.id) AS comments_count 
    FROM users 
    GROUP BY users.id, user.display_name
    HAVING comments_count > 150;
) AS T

Comments

[OMG Ponies]

"All these users with one row"? Can you please update the question with an example of your data, and what you want as actual output.

[WesternTune]

I mean i need only number representing all users who fit these criterias. It's like to wrap all this SQL into another: SELECT COUNT(CURRENT_WORKING_SQL) AS total_user_count;

[WesternTune]

Thanks for answer but actually nothing changes in result whenever I user GROUP statement or i don't. Just checked now on my db with "comments_count > 1". I got 807 users in both cases. And by the way, this doesnt solve problem of getting result in one row.

[Dan P]

recheck with last query if this is what you mean by one row

[WesternTune]

nope, it returns the quantity of comments per users. I have 2 users only wit such criteria. And this query return 2 numbers: "270", "384" when i need only one number: 2 (quantity of users);

[Mosty Mostacho]

Oh, got it. Updated my answer.