Solved

Query Help - Leaders

Posted on 2013-01-06
7
229 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to implement server side field validation and display customized error messages to the client.
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…
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 …

737 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