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

mysql sql subquery aggregate-functions
WesternTune picture WesternTune · Apr 5, 2012 · Viewed 47.6k times · Source

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.

Answer

Mosty Mostacho picture Mosty Mostacho · Apr 5, 2012

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

Related questions

Count rows with a specific condition in aggregate query

I have this query to get the number of PlayerSessions with reconnect = TRUE, grouped by Player.country: SELECT country, COUNT(*) AS with_reconnect FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id) WHERE reconnect = TRUE GROUP …

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
How to delete from select in MySQL?

This code doesn't work for MySQL 5.0, how to re-write it to make it work DELETE FROM posts where id=(SELECT id FROM posts GROUP BY id HAVING ( COUNT(id) > 1 )) I want to delete columns that dont have unique id. …

Read More