Prev: Update using SELECT
Next: Simulating Connection Error
From: mgm on 6 May 2010 14:05
This is the query that returns the result count, but I want to get the
count/totalresponses:

Select q.surveyquestionid, q.surveyquestion, c.surveychoiceid,
c.ordposition, c.surveychoice, count(r.surveyresultid) as ResultCount
from SurveyQuestion q
join surveychoices c on q.surveyquestionid = c.surveyquestionid
left join surveyresult r on r.surveychoiceid = c.surveychoiceid
where q.isenabled = 1 and c.isenabled = 1
group by q.surveyquestionid, q.surveyquestion, c.surveychoiceid,
c.ordposition, c.surveychoice
order by q.surveyquestionid, c.ordposition

I had this, but i have to manually change each questionid and I know there's
a better way:
declare @totalresponses float(1), @question int
set @question = 5
set @totalresponses = (select count(sr.surveyresultid) FROM SurveyQuestion q
join surveychoices c on q.surveyquestionid =
c.surveyquestionid
join surveyresult sr on sr.surveychoiceid =
c.surveychoiceid
where q.surveyquestionid = @question)
SELECT surveyquestion, c.ordposition, c.surveychoice,
count(sr.surveyresultid) as 'response_count'
, cast(count(sr.surveyresultid)/@totalresponses * 100 as numeric(5,1)) as
'percentage'

Thanks if you can help!
From: Plamen Ratchev on 6 May 2010 22:03
One way would be:

SELECT <columns>
FROM (<query_1_without_order_by>) AS A
JOIN (<query_2_without_predicate_for_surveyquestionid>) AS B
ON A.surveyquestionid = B.surveyquestionid;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Update using SELECT
Next: Simulating Connection Error