• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3017
  • Last Modified:

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.
0
lepirtle
Asked:
lepirtle
  • 2
  • 2
2 Solutions
 
SQL_SERVER_DBACommented:

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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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



0
 
lepirtleAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a querydef in access is a what is otherwise called a VIEW.

SELECT Q1.User, Q1.TotalMiles,
1 + (Select Count(*) from ( SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User ) where TotalMiles > Q1.TotalMiles) AS Ranking
FROM  ( SELECT User, Sum(Miles) AS TotalMiles
FROM mytable
WHERE (((Date)>#12/31/2006# AND (Date)<#1/1/2008#))
GROUP BY User ) S Q1
 ORDER BY Ranking DESC
0
 
lepirtleAuthor Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now