Getting column averages in report
I have created two separate queries that work rather nicely on their own. The first displays each survey number and the answers to each individual question:
The second finds the average score for each question in all survey responses:
Is there any way that I can display the responses for each survey followed by the average for each individual question at the bottom of a single report. Putting them in separate reports doesn't make a whole lot of sense.
select sd.surveyID
, max(case when sd.surQuesID = 1
then sd.SurAnsID end) as Q1
, max(case when sd.surQuesID = 2
then sd.SurAnsID end) as Q2
, max(case when sd.surQuesID = 3
then sd.SurAnsID end) as Q3
, max(case when sd.surQuesID = 4
then sd.SurAnsID end) as Q4
, max(case when sd.surQuesID = 5
then sd.SurAnsID end) as Q5
from SurveyDetails sd
group
by sd.surveyID;
The second finds the average score for each question in all survey responses:
select
avg(CASE WHEN sd.surquesID=1 THEN sd.surAnsID end) 'Q1 Avg.',
avg(CASE WHEN sd.surquesID=2 THEN sd.surAnsID end) 'Q2 Avg.',
avg(CASE WHEN sd.surquesID=3 THEN sd.surAnsID end) 'Q3 Avg.',
avg(CASE WHEN sd.surquesID=4 THEN sd.surAnsID end) 'Q4 Avg.',
avg(CASE WHEN sd.surquesID=5 THEN sd.surAnsID end) 'Q5 Avg.' from SurveyDetails sd ;
Is there any way that I can display the responses for each survey followed by the average for each individual question at the bottom of a single report. Putting them in separate reports doesn't make a whole lot of sense.