Solved

Query Help - Leaders

Posted on 2013-01-06
7
230 Views
Last Modified: 2013-01-06
Hello,

I have two tables

table 1: cms_members
id|username|public|challenge

table 2: cms_activities
date|user_id|miles|trail

I need to run a query where I can select the top 10 people and display them by rank.


ie

Leaders
username - unique trails - total days - total miles

I started with this but after staring at it for 30 minutes I thought I should ask for help.

SELECT
	cms_members.id,
	cms_members.username
FROM
	cms_members
WHERE
	challenge = 'ex'
AND public = 'Yes'

Open in new window


I am completely lost and would greatly appreciate any help.

Matt
0
Comment
Question by:movieprodw
[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
  • 3
7 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38748889
Hi prod,

the method of choosing the "top 10" will vary according to which SQL engine you're running, but the entire list can be chosen with:

SELECT * 
FROM cms_members
INNER JOIN cms_activites
  ON cms_members.id = cms_activites.user_id
ORDER BY miles desc;

Open in new window


Which database are you using?

Kent
0
 
LVL 1

Author Comment

by:movieprodw
ID: 38748898
I am using mysql
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38748914
Easy enough.  :)  LIMIT 10

SELECT * 
FROM cms_members
INNER JOIN cms_activites
  ON cms_members.id = cms_activites.user_id
ORDER BY miles desc
LIMIT 10;

Open in new window



Good Luck,
Kent
0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 
LVL 1

Author Comment

by:movieprodw
ID: 38748929
Hello Kdo,

Thank you for the help.

The issue is that it is not totaling the miles per user and 'ranking' them.

I hope I am explaining it correctly.

table 2: cms_activities
date|user_id|miles|trail
01/02/14|23|50|trail2
01/02/16|23|5|trail1
01/02/17|25|50|trail4
01/02/13|25|5|trail1
01/02/15|23|5|trail6
01/02/16|23|50|trail1
01/02/17|25|5|trail7
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 38748936
Sorry.  That query selects the longest trail, not the longest sum.

The simplest query just sums the data in the cms_activities table:

SELECT user_id, sum (miles) totalmiles
FROM cms_activities
GROUP BY user_id
ORDER BY 2
LIMIT 10;

Open in new window


From there you can add columns from either of the joined tables.

SELECT username, sum (miles) totalmiles 
FROM cms_members
INNER JOIN cms_activites
  ON cms_members.id = cms_activites.user_id
GROUP BY username
ORDER BY 2 desc
LIMIT 10;

Open in new window

                                           

The ORDER BY clause allows either column name or column number.  Some SQL engines allow the aliased name in the filter/control clauses, others do not.  But this form should be accepted by all SQL.


Kent
0
 
LVL 1

Author Comment

by:movieprodw
ID: 38748963
Thank you!
0
 
LVL 1

Author Closing Comment

by:movieprodw
ID: 38748964
Perfect
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

623 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