CODEWITHSUNDEEP
mysqlsqlsortingjoincount
Bhav
Bhav

Reputation: 2207

MySQL counting and sorting rows 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?

Upvotes: 0

Views: 41

Answers (2)

spencer7593
spencer7593

Reputation: 108490

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

Upvotes: 1

durbnpoisn
durbnpoisn

Reputation: 4669

You'd have to add a count to your query:

SELECT count(U.user_id) as UserCount, 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='0'
GROUP BY UserCount, Q.question_id, Q.author_id, Q.approved, U.role;

But this may still return too many rows, as they may all still be unique. You'd have to drop some rows out of the select if you want just raw counts. But that's the basic idea.

Upvotes: 0

Related Questions