SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    ǖber abstrakt's Avatar
    Join Date
    Dec 2001
    Location
    Earth
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting Maximum Sum

    Greetings friends

    I have a table called 'referrals', which has 2 fields:
    iUserId
    iCreditId

    iUserId is a lookup integer unique to a specific user (the referred user), and the iCreditId is a lookup integer tied to the specific user that gets the credit for the referral. I need to put togther a query to populate a variable with the number of referral credits the greatest referring user has at any given time. I know this requires a SUM query, but I am not exactly sure how to pull it off. Anyone mind assisting? Hopefully my question makes sense.

    For example:
    2 1
    3 1
    4 1
    5 1
    6 1
    7 2
    8 2

    That would mean iUserId 1 had 5 referrals, and iUserId 2 only had 2 referrals, so 5 would be the magic number variable output that I need since iUserId 1 was the highest referring user in the table.

    Can I do a SELECT UNIQUE MAX SUM () type query?

    Thank you!


  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select max(c) 
    from (select count(*) c
             from referrals
             group by iCreditId) dt

  3. #3
    ǖber abstrakt's Avatar
    Join Date
    Dec 2001
    Location
    Earth
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie
    Code:
     select max(c) 
     from (select count(*) c
              from referrals
              group by iCreditId) dt
    Thank you, but it's not working for me. Here's the line of code I used with my current database class:
    PHP Code:
    $iNumRef $db->get_var("SELECT max(c) FROM (select count(*) c FROM referrals GROUP BY iCreditId) dt"); 


  4. #4
    ǖber abstrakt's Avatar
    Join Date
    Dec 2001
    Location
    Earth
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    This is the query that ended up working for me:
    PHP Code:
    $iNumRef $db->get_var("SELECT SUM(iCreditId) FROM referrals GROUP BY iCreditId LIMIT 1"); 
    Eventually there will be about 500k records in this referrals table, will this above query take too much CPU? If so can someone suggest a less intensive query?

    Thank you!


  5. #5
    ǖber abstrakt's Avatar
    Join Date
    Dec 2001
    Location
    Earth
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I stand corrected, after populating more dummy data into my table, this is the correct query:
    Code:
       SELECT SUM(iCount) as iCount
       FROM referrals
        GROUP BY iCreditId
        ORDER BY iCount DESC
        LIMIT 1



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
  •