Solved

Combine two SQL queries

Posted on 2007-11-29
5
2,994 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
  • 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 142

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 142

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now