lepirtle
asked on
Combine two SQL queries
I have two SQL statements that I need to combine into one but I don't know the proper syntax.
The 2 statements are as follows:
Query1:
SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User
Query2:
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
ORDER BY Ranking DESC
The inner select counts records in Query1 where the totalmiles is greater than the current record. i.e. if there are 4 other users that have travelled further, then my ranking is.. 4, add 1 = 5.
The 2 statements are as follows:
Query1:
SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User
Query2:
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
ORDER BY Ranking DESC
The inner select counts records in Query1 where the totalmiles is greater than the current record. i.e. if there are 4 other users that have travelled further, then my ranking is.. 4, add 1 = 5.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your incredibly quick responses. I have been experimenting with your solutions trying to figure out how to utilize them in my "single" query but still cannot produce the results I need. I believe it is because I must "nest" the Q1 somewhere within the query itself and I don't know how do that.
For example, using sql_server_dba's solution:
SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
union
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
Don't I have to actually insert the Q1 query somewhere inside the above solution since the solution doesn't "know" what "Q1.user" or "Q1.TotalMiles" is?
And to use angelIII's solution requires using a "querydef". I don't know what a "querydef" is.
Sorry to be so unintelligent regarding this subject.
If it matters, I am using a MS Access database accessed using Coldfusion and Coldfusion is expecting to see something like:
<cfquery name=query" datasource="dsn_name"
SQL statement
</cfquery>
That is why I am trying to combine the 2 SQL statements into one - so that they can be inserted into the single Coldfusion query tag.
I hope this explains my reason for finding the syntax to combine all the SQL into one statement.
Thanks,
Lee
For example, using sql_server_dba's solution:
SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
union
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from Query1 Where TotalMiles > Q1.TotalMiles) AS Ranking
FROM Query1 AS Q1
Don't I have to actually insert the Q1 query somewhere inside the above solution since the solution doesn't "know" what "Q1.user" or "Q1.TotalMiles" is?
And to use angelIII's solution requires using a "querydef". I don't know what a "querydef" is.
Sorry to be so unintelligent regarding this subject.
If it matters, I am using a MS Access database accessed using Coldfusion and Coldfusion is expecting to see something like:
<cfquery name=query" datasource="dsn_name"
SQL statement
</cfquery>
That is why I am trying to combine the 2 SQL statements into one - so that they can be inserted into the single Coldfusion query tag.
I hope this explains my reason for finding the syntax to combine all the SQL into one statement.
Thanks,
Lee
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks sql_server_dba and angelIII. I still don't know how to combine the queries into one SQL statement so that it will work in my Coldfusion query but I will continue to experiment with your solutions because I am sure that it is my lack of knowledge in understanding what you have given me that is the source of my problem.
you need to create a view (querydef) with the first query, say with the name user_total_miles :
and you final query:
SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from user_total_miles where TotalMiles > Q1.TotalMiles) AS Ranking
FROM user_total_miles S Q1
ORDER BY Ranking DESC