SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Rank query

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rank query

    I am looking for a kind help how to do a rank query. I need to find out the rank of a sales agent who is doing multiple leads and generating revenues. Rank would be on based on the revenue from leads table. Query will sum the revenue amounts of all of his leads and then give me the the rank of that user.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    use windowing functions, specifically RANK()

    if your dbms, which you neglected to mention, does not support window functions, it's gonna be a lot tougher
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using mysql and it doesn't support RANK() function. Can i have any help for it please.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Henson4004 View Post
    Can i have any help for it please.
    sure

    let's start with the query that produces a single agent's revenue

    could you show that, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Single agent revenue is generated:

    SELECT SUM(marketing_commission+purchaser_commission) AS Revenue FROM operations_history WHERE user_id=22 AND date_range LIKE 'date("Y-m")'

    where user_id is the id of agent and date_range is for month.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT t.Revenue
         , ( SELECT COUNT(*)+1
               FROM ( SELECT user_id
                           , SUM(marketing_commission + 
                                 purchaser_commission) AS Revenue
                        FROM content_type_results
                       WHERE date_range LIKE 'date("Y-m")'
                      GROUP
                          BY user_id  ) AS m
              WHERE m.Revenue > t.Revenue ) AS rank
      FROM ( SELECT SUM(marketing_commission + 
                        purchaser_commission) AS Revenue 
               FROM operations_history 
              WHERE user_id = 22 
                AND date_range LIKE 'date("Y-m")' ) AS t
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks much appreciated. Could you please tell me what is this table: content_type_results it was not in my query.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Henson4004 View Post
    Could you please tell me what is this table: content_type_results it was not in my query.
    sorry, that was a copy/pasta error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •