Solved

Combine two SQL queries

Posted on 2007-11-29
5
3,003 Views
Last Modified: 2008-02-01
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
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
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 250 total points
ID: 20374553

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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20374564

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
 

Author Comment

by:lepirtle
ID: 20375019
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 20375643
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
 

Author Comment

by:lepirtle
ID: 20375970
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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