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

x
?
Solved

Include additional fields in an SQL ranking statement

Posted on 2007-12-06
2
Medium Priority
?
350 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 2000 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

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

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 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