Solved

Get the highest sum from a MySQL Table

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
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 …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

863 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

28 Experts available now in Live!

Get 1:1 Help Now