Alex Rivera | Logout

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

Asked 2012-04-05T04:05:13.113
16

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.

mysql sql subquery aggregate-functions
Edit
Report

2 Answers

15

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
answered 2012-04-05T04:26:44.657
10

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
answered 2012-04-05T04:16:16.290

Your Answer