Results 1 to 5 of 5
Thread: Selecting Maximum Sum
Jan 7, 2006, 18:15 #1
Selecting Maximum Sum
I have a table called 'referrals', which has 2 fields:
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.
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?
Jan 8, 2006, 09:16 #2
- Join Date
- Jan 2004
- Uppsala, sverige
- 2 Post(s)
- 1 Thread(s)
select max(c) from (select count(*) c from referrals group by iCreditId) dt
Jan 8, 2006, 22:45 #3Originally Posted by swampBoogie
$iNumRef = $db->get_var("SELECT max(c) FROM (select count(*) c FROM referrals GROUP BY iCreditId) dt");
Jan 8, 2006, 22:59 #4
This is the query that ended up working for me:
$iNumRef = $db->get_var("SELECT SUM(iCreditId) FROM referrals GROUP BY iCreditId LIMIT 1");
Jan 8, 2006, 23:47 #5
I stand corrected, after populating more dummy data into my table, this is the correct query:
SELECT SUM(iCount) as iCount FROM referrals GROUP BY iCreditId ORDER BY iCount DESC LIMIT 1