How can I use the MySQL COUNT() statement on a generated query?

I’ve created a query that finds the users of a specific site that have liked every photo on the site and stand out as potential bots.

I’m trying to count the number of bots.

My query currently returns all the bot usernames and the number of photos that they’ve liked, but I’m having trouble simply counting them.

It would be ideal to be able to COUNT(*) on the table that the below query generates.

SELECT
    username,
    COUNT(*) AS total_likes
FROM users
JOIN likes
    ON likes.user_id = users.id
GROUP BY likes.user_id
HAVING total_likes = (SELECT COUNT(*) FROM photos);

Answer

You can do this with a subquery:

SELECT COUNT(*) as num_potential_bots
FROM (SELECT u.username, COUNT(*) AS total_likes
      FROM users u JOIN
           likes l
           ON l.user_id = u.id
      GROUP BY l.user_id
      HAVING total_likes = (SELECT COUNT(*) FROM photos)
     ) u;

Advertisement

Answer