lepirtle
asked on
Combine two SQL queries in a cfquery
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].[Sum
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
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].[Sum
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html
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].[Sum
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.
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].[Sum
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.
ASKER
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
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
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].[Sum
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.
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].[Sum
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.
ASKER
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
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
ASKER
<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\milelo
36 : </cfquery>
37 :
38 : <cfquery name="qrosenthal1" datasource="milelog">
39 : SELECT u.username, sum(m.miles)
40 : FROM tbl_mileusers u, tbl_miles m