CODEWITHSUNDEEP
phpmysqlsql
Takami
Takami

Reputation: 115

SQL Query can't find the way to get the result

I can't find a way to get this result:

|-------------|--------|
| username    |  rank  |
--------------|--------|
| takami      |  25    |
| mnk         |  24    |
------------------------

I need a list of all users with it's ranks, where the rank is a SUM of count of all questions and answers made by that user, can someone help me making the query?, i tried for hours and i can't make a query get this result.

This is the model of DB

Thanks very much

Upvotes: 1

Views: 86

Answers (1)

David Lin
David Lin

Reputation: 13353

I believe there will be answers using sub-queries, but I would like to give you a answer without sub-queries:

SELECT user.name, count(distinct question.id) + count(distinct answer.id) from user 
left join
answer on user.id = answer.user_id
left join
question on user.id =question.user_id
group by user.name

Please have a look at the sqlfiddle

Upvotes: 2

Related Questions