working with the results of a subquery

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

SQL Server Development (2000)

working with the results of a subquery

Author

Topic

Southpaw
Starting Member

6 Posts

Posted - 2001-11-14 : 10:27:09

I have a stored procedure that returns the answers of a survey (poll) along with how many times a particular answer has been picked (voted). The SP also returns a column that shows you how many percent chose each answer. A typical internet survey (poll).
I use a subquery to count the votes on each answer. Then I seem to need that subquery again for the percantage calculations.
I was wondering whether it was possible to just use the results of the first subquery for the percentage calculations without having to run the subquery again.

Here´s the procedure:
CREATE PROCEDURE sp_GetSurveyAnswers (@survey_id INT) AS

-- Declare variables
DECLARE @total_points FLOAT(24)

-- Get the total point count
SET @total_points = ISNULL((SELECT COUNT(sv.answer_id)
FROM survey_votes AS sv
INNER JOIN survey_answers AS sa ON sv.answer_id = sa.answer_id
WHERE AND sa.survey_id = @survey_id), 0)

-- Can't divide by zero can we
IF @total_points = 0
SET @total_points = 1

-- Get the answers
SELECT s.answer_id,
s.answer,
ISNULL((SELECT COUNT(sv.answer_id)
FROM survey_votes AS sv
WHERE sv.answer_id = s.answer_id), 0) AS answer_points,
CAST(ROUND((ISNULL((SELECT COUNT(sv.answer_id)
FROM survey_votes AS sv
WHERE sv.answer_id = s.answer_id), 0) / @total_points) * 100 , 0) AS TINYINT) AS point_percentage
FROM survey_answers AS s
WHERE s.survey_id = @survey_id

-- Southpaw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources