|
I'm trying to retrieve a list of users with the most points based on post votes received and business submissions.
The problem is that the ordering of the users are not correct. The query above returns the users with the most votes even though I want the business count to have more weight.
I need the query to return
username, total_pts
Also business submission are suppose to be worth more; hence, I multiply the count of businesses by 10.
Author Table
Id
username
Business Table
id
author_user_id
Post Table
id
author_user_id --------->points to id in User table
reply_user_id --------->points to id in User table
post
total_votes
SELECT u.id, u.username, (sum(total_votes) + count(b.id)*100) as totalpts
FROM user u, business b, post p
Where p.total_votes is not NULL
And ((u.id = p.author_user_id or u.id=p.reply_user_id )
OR b.author_user_id = u.id )
group by u.id
order by totalpts desc
|
