Need advice on how to do this in a single MySQL query

Question

Is this possible to do this in one MySQL query? Basically I need to sort users by how many responses they have. I have 2 tables

table "users":
id      username
------------------
1       hunter
2       loserville

and another

table "responses":
id      user_id     response
-------------------------------
1         1            yes
1         2            yes
1         1            no
1         1            yes

I need something like this

SELECT users.id
FROM users
    UNION ALL 
        SELECT COUNT(responses.id) As num_responses
        FROM responses
        WHERE user_id = users.id
ORDER BY num_responses DESC

Unfortunately it doesn't work. Any suggestions? Let me know if you are confused! Thanks for your time.

Answer 1

SELECT users.id, users.username, count(*) as responses
FROM   users LEFT JOIN responses
       ON users.Id = responses.user_id
GROUP BY users.id, users.username
ORDER BY count(*) DESC

Answer 2

I guess what you want to use is the GROUP BY clause.

You'd want a query like

SELECT users.id, COUNT(*) AS responses 
    FROM users JOIN responses ON users.id = responses.user_id 
    GROUP BY user_id ORDER BY responses DESC

(if you don't want any information from the users table, you can leave out the JOIN and only do the query on the responses.

The join lists all users with their responses and then counts the number of entries.

Answer 3

This

SELECT  u.id, COUNT(r.id) AS cnt
FROM    users u
LEFT JOIN
        responses r
ON      r.user_id = u.id
        AND r.response = 'yes'
GROUP BY
        u.id
ORDER BY
        cnt DESC

or this:

SELECT  u.id,
        (
        SELECT  COUNT(*)
        FROM    responses r
        WHERE   r.user_id = u.id
                AND r.response = 'yes'
        ) cnt
FROM    users u
ORDER BY
        cnt DESC

The former is generally faster in InnoDB, the latter in MyISAM.