How to construct this query? (Ordering by COUNT() and joining with users table)

Question

users table:

id-user-other columns

scores table:

id-user_id-score-other columns

They're are more than one rows for each user, but there's only two scores you can have. (0 or 1, == win or loss). So I want to output all the users ordered by the number of wins, and all the users ordered by the numbers of losses.

I know how to do this by looping through each user, but I was wondering how to do it with one query. Any help is appreciated!

Answer 1

Try something like this:

SELECT u.user, 
       COALESCE(SUM(s.score), 0) AS WINS,
       SUM(CASE WHEN s.score = 0 THEN 1 ELSE s.score END) AS LOSSES 
  FROM users u
       LEFT JOIN scores s
       ON u.id = s.user_id
GROUP BY u.user
ORDER BY WINS, LOSSES

Answer 2

if you want two outputs you need two queries

SELECT u.user,
COUNT(s.score) as WINS
FROM users u INNER JOIN scores s
     ON u.user_id = s.user_id 
WHERE s.score = 1
GROUP BY u.user_id (and any other columns you need from the user table)
ORDER BY WINS

You then do another query for the losses.
Essentially this is the same as Adams response, but his response will give you a list of users ordered by wins, then ordered by losses. To get losses, just change the s.score=1 to s.score = 0