Solved

Combine two SQL queries in a cfquery

Posted on 2007-11-29
8
1,205 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 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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