Solved

Query Help - Leaders

Posted on 2013-01-06
7
226 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
  • 4
  • 3
7 Comments
 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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:
Kdo 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 36
Decrypt string by php 7 31
SQL Syntax: How to force case sensitive query? 2 30
SQL Query assistance 16 26
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

778 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