Query the results of a query? - 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 2000 Forums

Transact-SQL (2000)

Query the results of a query?

Author

Topic

jpk79
Starting Member

4 Posts

Posted - 2006-09-05 : 13:48:05

Is there any way in Query Analyzer to query the results of a query that was just run? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 13:53:23

yes. make the query just run a derived table.

select * from (
...the query last run...
)

Peter Larsson
Helsingborg, Sweden

jpk79
Starting Member

4 Posts

Posted - 2006-09-05 : 15:35:53

Thanks Peter. Forgive me, I am new to SQL, and not sure I am following the derived table query.

For example, my original query is this:

select username, count(distinct productnm)
from main
where (status = N'stopped')
group by username

Once I get those results, I want to query the results to get a SUM of the productnm column. So per your example, would I do the following?

select SUM(distinct productnm)
from (select username, count(distinct productnm)
from main
where (status = N'stopped')
group by username)

I tried this and it failed to execute. I am sure I am off, so any clarification / assistance is greatly appreciated. Thanks.

kevindockerty
Starting Member

27 Posts

Posted - 2006-09-05 : 16:24:22

something like this might work

select sum(mytotal) from
(
select username, count(distinct productnm) as mytotal
from main
where (status = N'stopped')
group by username
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-05 : 16:25:29

Sql has a requirment that derived tables be "aliased" so Peter's example may just need an alias after the final parenthesis. often you just see people put a single "a" as an alias:
select * from (
...the query last run...
) a

I'm not sure a SUM of a distinct COUNT by User makes sense but this should at least not error out:


select	sum(DistProdCountByUser) as SumDistProdCountByUser
from	(
	select username
		,count(distinct productnm) as DistProdCountByUser
	from	main
	where	(status = N'stopped')
	group by username
	) as derivedTable

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources