?
Solved

Combine two SQL queries in a cfquery

Posted on 2007-11-29
8
Medium Priority
?
1,216 Views
Last Modified: 2008-02-01
I am using MS Access and Coldfusion to create a cfquery that will output a list containing the number of miles that users have accumulated during a current year and list those users and their accumulated miles, in descending order with a ranking number of 1 for the user with the most miles.

tbl_mileusers has 2 fields: userkey and username.
tbl_miles has 4 fields: userkey, milesdate, and miles.

Using Access' Query Design mode, I have created two queries:
qry 1 reads:
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;

qry 2 reads:
SELECT myqry2.username, myqry2.SumOfmiles, (Select Count(*) from myqry1 Where [Sumofmiles]>[myqry2].[SumOfmiles])+1 AS Ranking
FROM myqry1 AS myqry2
ORDER BY myqry2.SumOfmiles DESC;

Running qry2 in Access produces exactly the output that I want. However, qry2 requires using qry1 in its SQL statement and I do not know the proper syntax to combine the 2 queries into 1 SQL statement which I need to construct my <cfquery>. (At least I assume that I need only 1 SQL statement to create a <cfquery> because I do not know any other way to create a cfquery).

Might someone provide me with the correct SQL to use to create my cfquery?

I am a beginner at this so if I am missing something obvious I appreciate your patience.
Thanks
Lee

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
  • 4
  • 2
  • 2
8 Comments
 
LVL 15

Accepted Solution

by:
danrosenthal earned 2000 total points
ID: 20376565
Try this query, the ranking is not in the select, but the order of the output is the in rank order, so therefore the 1st record is rank #1, the 2nd record is ranked #2 and so on:

SELECT u.username, sum(m.miles)
FROM tbl_mileusers u, tbl_miles m
WHERE u.userkey = m.userkey
GROUP BY u.userkey
ORDER BY 2 DESC

0
 

Author Comment

by:lepirtle
ID: 20376689
Using your code:
<cfquery name="qrosenthal1" datasource="milelog">
SELECT u.username, sum(m.miles)
FROM tbl_mileusers u, tbl_miles m
WHERE u.userkey = m.userkey
GROUP BY u.userkey
ORDER BY 2 DESC
</cfquery>

I received the following error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'username' as part of an aggregate function.
 
The error occurred in E:\web\lepirtle\lib\milelog\test1.cfm: line 38

36 : </cfquery>
37 :
38 : <cfquery name="qrosenthal1" datasource="milelog">
39 : SELECT u.username, sum(m.miles)
40 : FROM tbl_mileusers u, tbl_miles m
0
 

Author Comment

by:lepirtle
ID: 20377050
danrosenthal,
I found the error!

Your SQL should have read:
SELECT u.userkey, sum(m.miles) AS summiles
FROM tbl_mileusers u, tbl_miles m
WHERE u.userkey = m.userkey
GROUP BY u.userkey
ORDER BY 2 DESC

With that change the output reads correctly!! Thanks very much.

Is it possible to output the ranking of each userkey? That is for the userkey with the most accumulated miles they would receive the ranking of "1" and the runner-up the ranking of "2"?

I thought that I had this request in my original question but if you would like I will make it a separate question so as to give you additional points. Please advise.
Thanks very much.
0
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 
LVL 15

Expert Comment

by:danrosenthal
ID: 20377266
I don't have time to write any queries, but here is a promising article about how to do exactly what you are asking:
http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html
0
 
LVL 12

Expert Comment

by:PCableGuy
ID: 20379112
Hi Lee,

Thanks for the points in the other thread.

I might be wrong and do not wish to complicate things, but I just wanted to mention that you might need to consider the effect of a "sum of miles tie" and then using the row number as the rank number. What happens, for example Row 1 user and Row 2 user have the same sum of miles. If you return Row number 2, he/she is ranked as #2, but in reality is tied for first.

The SQL below combines the queries into one query and puts the rank into the database. If there is a tie, he/she will have the same rank. It's ugly, but it worked on my little database.

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;


Please post  again  (somewhere) if you have any questions.
0
 

Author Comment

by:lepirtle
ID: 20381381
Hello PCableGuy,
You are very welcome for the points - you earned them. Thank you so much.

I hadn't considered the possibility of a tie but I am sure that any of my users would and they would let me know about it. <g>. So thanks for saving me from that possibility.

You deserve additional points bu I don't know how to get them to you for this solution so let me propose a solution: The last bit of code that I am puzzled about is on a page that would pertain to an individual user, I would like to create a line of text that says something like "You are in nnn place among all nnn riders this year". I know how to derive the total number of riders but I don't know how to produce the individual's own ranking though I assume it is related to the above SQL that you provided me. The individual's userkey will be passed to the page where the SQL appears so that the SQL could read something like "use the ranking WHERE userkey = #cookie.userkey#".

If/when you have a solution to that problem, let me know in this discussion and I will open a new question so that you can answer it and get the points immediately.

I realize that this sounds like I am "bribing" you to help me but you are already familiar with my project and I would like you to have the points.
Thanks,
Lee
0
 
LVL 12

Expert Comment

by:PCableGuy
ID: 20386633
Save as myqry2 :

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;

This query should return the rank of an individual:
SELECT Ranking As Rank
FROM myqry2
WHERE (SELECT tbl_mileusers.username
FROM tbl_mileusers
WHERE tbl_mileusers.userkey= #cookie.userkey#) = myqry2.username;

Be careful, it uses the username to grab the rank, so you can't have duplicate username in the tbl_mileusers table. This is because myqry2 doesn't contain the userkey, just the username. If it works don't worry about the points, not a problem for me, but EE might frown if we keep using this thread.


0
 

Author Comment

by:lepirtle
ID: 20388135
PCableGuy,
Once again I thank you for your very clear, generous, and accurate advice. I wish that I was allowed to have open ed this question so that you could have received the points you most certainly have earned.
Thanks again,
Lee
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

770 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