Solved

Get the highest sum from a MySQL Table

Posted on 2009-04-12
4
364 Views
Last Modified: 2013-12-12
Give a table that has two fields; user_id [INT] and user_minutes [INT]. How can I formulate a MySQL query to get the top x of the sum of the user_minutes.

In other words, this table has a bunch of records...a user_id could be repeated many times with an associated number of minutes. I want to find out which user_id has the highest amount of minutes (the sum of minutes in the entire table). Can I do this with MySQL? I'm using PHP as well if that helps.
0
Comment
Question by:DVation191
4 Comments
 
LVL 19

Accepted Solution

by:
Kim Ryan earned 400 total points
ID: 24127251
You could try a query like this

select user_id, sum(user_minutes) as total_minutes
from your_table
group by user_id
order by total_minutes descending
0
 
LVL 5

Assisted Solution

by:Trakos
Trakos earned 50 total points
ID: 24127506
Also, to get only x top entries add "limit x" at the end of the query. Like

select user_id from yourtable group by user_id order by sum(user_minutes) desc limit x;
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 50 total points
ID: 24127789

Trakos solutin is fine for you to get the top x user_ids in your table who have more sum(user_minutes).
If you want to only one user_id who is top xth (1st, 2nd, or 3rd etc) in therms of minutes, you can use the offset for the limit.

select user_id from yourtable group by user_id order by sum(user_minutes) desc limit x,1;
replace x with 0 (for top 1st), 1 (for top 2nd), 2 (top 3rd) etc...
0
 
LVL 20

Author Comment

by:DVation191
ID: 24128910
Perfect! Thank you all for contributing.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
spacing 5 31
Creating a slider 12 34
Google maps, choose location using pin 10 19
Programatically extract date from website 8 19
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now