Solved

Get the highest sum from a MySQL Table

Posted on 2009-04-12
4
390 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

860 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