[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Get the highest sum from a MySQL Table

Posted on 2009-04-12
4
Medium Priority
?
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 19

Accepted Solution

by:
Kim Ryan earned 1600 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 200 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 41

Assisted Solution

by:Sharath
Sharath earned 200 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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 count occurrences of each item in an array.
Suggested Courses

649 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