MySQL sort and sort lines are returned from a query

I have the following MySQL tables with the following fields:

"questions"

question_id (PK, AI), author_id (FK), approved ...

"users"

user_id (PK, AI), username, role ...

I want to return the usernames of those who have the most questions and where approved equals 'Y' in descending order but the role must equal '1'.

For example, assuming all users have a role equaling '1', the following data should return the username field for author_id 2, followed by author_id 1.

question_id author_id  approved
--------    -----      ----
1           1          Y
2           1          N
3           2          Y
4           2          Y
5           3          N

So far, I've got the following:

SELECT Q.question_id, Q.author_id, Q.approved, U.role
FROM p1209279x.questions Q
LEFT JOIN p1209279x.users U
ON U.user_id=Q.author_id
WHERE approved='Y' AND role='1';

But this returns multiple rows with the same user_id so how do I count the rows for each user_id returned and output their respective usernames?


Just add an aggregate function (e.g. COUNT() or SUM()) in the SELECT list, and add a GROUP BY clause to the query, and an ORDER BY clause to the query.

SELECT U.username
     , COUNT(Q.question_id)
  FROM ...

 GROUP BY Q.author_id
 ORDER BY COUNT(Q.question_id) DESC


Note that the predicate on the role column in the WHERE clause of your query negates the "outerness" of the LEFT JOIN operation. (With the LEFT JOIN, any rows from Q that don't find a matching row in U, will return NULL for all of the columns in U. Adding a predicate U.role = '0' in the WHERE clause will cause any rows with a NULL value in U.role to be excluded.


This would return distinct values of username, along with a "count" of the questions related to that user:

SELECT U.username
     , COUNT(Q.question_id)
  FROM p1209279x.questions Q
  JOIN p1209279x.users U
    ON U.user_id=Q.author_id
 WHERE Q.approved='Y'
   AND Q.role='0'
 GROUP BY Q.author_id
 ORDER BY COUNT(Q.question_id) DESC