Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query Help - Leaders

Posted on 2013-01-06
7
Medium Priority
?
231 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 46

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 46

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
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
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 46

Accepted Solution

by:
Kent Olsen earned 2000 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

Implementing Azure Infrastructure Exam 70-533

This course is designed to familiarize and instruct students in the content that is covered by Microsoft Exam 70-533, Implementing Microsoft Azure Solutions. It focuses on all the November 2016 objective domain topics.

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 four methods for overlaying images in a container on a web page
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 look for a specific file type in a local or remote server directory using PHP.

715 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