How to Query Query Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to Query Query Results

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2011-07-06 : 16:28:00

Hi,

I am trying to figure out how I can Query the results of another query. Is this possible?

Thanks,

Nick

Example

Select *
FROM TABLE
WHERE Column = 1

SELECT *
FROM RESULTS of First Query

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-06 : 17:13:42

You can use it as a derived table, like this:

SELECT * FROM
(SELECT Column1, Column2 FROM Table1 WHERE ...) dt
WHERE ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-06 : 17:13:57

What is the difference between those two result sets? Aren't they the same?

However, one way to answer your question is by using a derived table:


select [type], count(*) [typeCount]
from   (
       select id, name, [type] 
       from sysobjects
       ) d
group by [type]
order by 2 desc

Be One with the Optimizer
TG

nhess80
Yak Posting Veteran

83 Posts

Posted - 2011-07-06 : 18:16:52

quote:
Originally posted by TG

What is the difference between those two result sets? Aren't they the same?

However, one way to answer your question is by using a derived table:

select [type], count(*) [typeCount]
from (
select id, name, [type]
from sysobjects
) d
group by [type]
order by 2 desc

Be One with the Optimizer
TG

Well what Im trying to do is this

SELECT SUM(RoundDuration) AS Duration,COUNT(CASE WHEN Duration > '0' THEN '1' END) AS Completes,
SUM(Revenue) / SUM(RoundDuration) AS Rate
FROM CDRS

But I want to group by SUM(Revenue) / SUM(RoundDuration)

which I am not able to...so i was just trying to find a way around that and i thought that if i ran my first query then queried the results like:

Select SUM(Duration),SUM(Completes),Rate

FROM First_Query

Group by Rate

nhess80
Yak Posting Veteran

83 Posts

Posted - 2011-07-06 : 18:17:37

quote:
Originally posted by tkizer

You can use it as a derived table, like this:

SELECT * FROM
(SELECT Column1, Column2 FROM Table1 WHERE ...) dt
WHERE ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-06 : 23:03:42

Yes you can do that, just like we showed you with our derived table examples:

Select SUM(Duration),SUM(Completes),Rate
FROM
(
SELECT SUM(RoundDuration) AS Duration,COUNT(CASE WHEN Duration > '0' THEN '1' END) AS Completes,
SUM(Revenue) / SUM(RoundDuration) AS Rate
FROM CDRS
) dt
Group by Rate

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ankie
Starting Member

2 Posts

Posted - 2011-07-06 : 23:03:55

tkizer already provided you with the solution. you just need to apply your query into the solution.

SELECT * FROM
(SELECT Column1, Column2 FROM Table1 WHERE ...) dt
WHERE ...

Select Rate, sum(duration), sum(Completes) from
(SELECT SUM(RoundDuration) AS Duration,COUNT(CASE WHEN Duration > '0' THEN '1' END) AS Completes,
SUM(Revenue) / SUM(RoundDuration) AS Rate
FROM CDRS) dt
group by Rate

nhess80
Yak Posting Veteran

83 Posts

Posted - 2011-07-07 : 13:42:16

Thank you very much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-07 : 14:15:38

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources