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

aggregate-functions, mysql, sql, subquery

Solution

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

Problem

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.

Original source