How to Query 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)

How to Query a Query

Author

Topic

ksubramonian
Starting Member

5 Posts

Posted - 2003-04-03 : 03:07:50

Hi!
I am using a select statement which looks something like the below.
**********************************************************
SELECT dbo.day_detail.days AS NameBoard, COUNT(a.days_to_receive) AS Total
FROM (SELECT survey_id, days_to_receive
FROM survey_data) a RIGHT OUTER JOIN
dbo.day_detail ON a.days_to_receive = dbo.day_detail.id
GROUP BY a.days_to_receive, dbo.day_detail.days
*************************************************************
And i get the result something like the below.
----------------
NAMEBOARD TOTAL
----------------
1week 5
2weeks 10
1month 5

*************************************************************
Now my doubt is how should i get the result as
--------------------------
NAMEBOARD TOTAL Average%
--------------------------
1week 5 25
2weeks 10 50
1month 5 25
______________________________________________
the condition is USING ONE SINGLE QUERY

Thanks in advance
Subramonian k

Edited by - ksubramonian on 04/03/2003 03:09:50

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-03 : 03:33:38

something like
select a.NameBoard, a.Total, perc = 1.0 * Total * 100 / b.tot
from
(
SELECT dbo.day_detail.days AS NameBoard, COUNT(a.days_to_receive) AS Total
FROM (SELECT survey_id, days_to_receive
FROM survey_data) a RIGHT OUTER JOIN
dbo.day_detail ON a.days_to_receive = dbo.day_detail.id
GROUP BY a.days_to_receive, dbo.day_detail.days
) as a,
(SELECT tot = count(*)
FROM survey_data) a RIGHT OUTER JOIN
dbo.day_detail ON a.days_to_receive = dbo.day_detail.id) as b

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-03 : 07:56:35

Of course watch out for divide-by-zero errors...make sure you handle any cases with zero as the denominator.

OS

ksubramonian
Starting Member

5 Posts

Posted - 2003-04-04 : 13:09:27

Thanks Guys.Great Job.
It works!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources