Solved

Combine two SQL queries in a cfquery

Posted on 2007-11-29
8
1,186 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 15

Accepted Solution

by:
danrosenthal earned 500 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now