Add two table vales and group by

Discussion in 'Databases' started by UCDaZ, Feb 16, 2009.

0
  1. #1
    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
     
    UCDaZ, Feb 16, 2009 IP
  2. dowhile

    dowhile Active Member

    Messages:
    37
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    93
    #2
    Maybe you should try something like this

    SELECT u.id, u.username, (sum(total_votes) + count(b.id)*100) as totalpts, count(b.id) as bizpts
    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 bizpts desc, totalpts desc
     
    dowhile, Feb 17, 2009 IP