asked on

Help with AVG

Hi,

How can I get the average/sum of a column in this query. The query has about 30 records in it, here is the SQL I'm using to get the data out.

SELECT q.SurveyID, q.questionID, q.QuestionScore, q.QuestionName
FROM Questions q

surveyID | questionID | QuestionScore | QuestionName

28323 2 45 Do you like food?
34852 2 88 Do you like food?
38052 2 14 Do you like food?
28323 55 15 Do you like milk?
34852 55 28 Do you like milk?
38052 55 48 Do you like milk?

I want to return one row with an average score here for each questionID. So I'm trying to get just two rows back.

How can I modify my query to do this?

Thanks...

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Éric Moreau

I think jogos forgot to remove D from the SELECT list. it should be:

SELECT q.questionID, AVG(q.QuestionScore)
FROM Questions q
group by q.questionID

keyu

It will return just 2 rows

SELECT questionID,QuestionName, [28323], [34852] ,[38052]
FROM (select questionID,SurveyID,QuestionScore,QuestionName
from Questions) as sourcetable
pivot
(
avg(q.QuestionScore) for SurveyID in (28323,34852,38052)
) as pvt
orderby pvt.questionID

jogos

<<It will return just 2 rows>>
That is conform the question
<<I want to return one row with an average score here for each questionID. So I'm trying to get just two rows back.>>

jogos

Did you already tried the suggested comments?

SELECT  q.questionID, AVG(q.QuestionScore)
FROM Questions q
group by  q.questionID

Open in new window

With name

SELECT  q.questionID, AVG(q.QuestionScore), q.QuestionName
FROM Questions q
group by  q.questionID, q.QuestionName
--or 
SELECT  q.questionID, AVG(q.QuestionScore), max(q.QuestionName)
FROM Questions q
group by  q.questionID

Open in new window

ValentinoV

Westside2004: what jogos posted is exactly what you've asked for. Please post follow-up comments if you're looking for something else or have some sort of issue with the answer.