I have the following MySQL tables with the following fields:
question_id (PK, AI), author_id (FK), approved ...
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.
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