Solved

Include additional fields in an SQL ranking statement

Posted on 2007-12-06
2
348 Views
Last Modified: 2010-05-18
I have an SQL statement that an Experts Exchange expert constructed for me that works perfectly allowing me to output a ranking order listing the ranking number, the user name for that ranking, and the sum of miles for that user.
The results of that query is at http://www.lepirtle.com/lib/milelog/test18.cfm and that code is as follows:
SELECT myqry2.username, myqry2.SumOfmiles, (Select Count(*) from (SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
FROM tbl_mileusers
INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
GROUP BY tbl_mileusers.username)
WHERE [Sumofmiles]>[myqry2].[SumOfmiles])+1 AS Ranking
FROM (SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
FROM tbl_mileusers
INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
GROUP BY tbl_mileusers.username) AS myqry2
ORDER BY myqry2.SumOfmiles DESC;

However,  I would like to add the users town and state to that output but haven't been successful in modifying the SQL statement. The two fields that I want to output are stored in the tbl_mileusers field and are named tbl_mileusers.town, and tbl_miles.st. I have experimented with the following statement:
SELECT myqry2.username, myqry2.SumOfmiles, tbl_mileusers.town, tbl_mileusers.st, (Select Count(*) from (SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
FROM tbl_mileusers
INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
GROUP BY tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st)
WHERE [Sumofmiles]>[myqry2].[SumOfmiles])+0 AS Ranking
      FROM
            (SELECT tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st, Sum(tbl_miles.miles) AS SumOfmiles
            FROM tbl_mileusers
            INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
            WHERE year(tbl_miles.milesdate) = Year(Date())
            GROUP BY tbl_mileusers.username,tbl_mileusers.town, tbl_mileusers.st) AS myqry2
ORDER BY myqry2.SumOfmiles DESC;
However as you can see from the results at http://www.lepirtle.com/lib/milelog/test19.cfm, the "ranking" number is erroneous.
Can someone please correct my error?
Thanks.

0
Comment
Question by:lepirtle
[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
2 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20424444
SELECT myqry2.username, myqry2.town, myqry2.st, myqry2.SumOfmiles, (
      Select Count(*) from (
            SELECT tbl_mileusers.username, Sum(tbl_miles.miles) AS SumOfmiles
            FROM tbl_mileusers
            INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
            GROUP BY tbl_mileusers.username) AS X
      WHERE [Sumofmiles]>[myqry2].[SumOfmiles]) +1 AS Ranking
FROM (
      SELECT tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st, Sum(tbl_miles.miles) AS SumOfmiles
      FROM tbl_mileusers
      INNER JOIN tbl_miles ON tbl_mileusers.userkey = tbl_miles.userkey
      GROUP BY tbl_mileusers.username, tbl_mileusers.town, tbl_mileusers.st) AS myqry2
ORDER BY myqry2.SumOfmiles DESC;

0
 

Author Comment

by:lepirtle
ID: 20424502
You nailed it!!
Thank you so very much.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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