Solved

Help with AVG

Posted on 2012-03-22
7
349 Views
Last Modified: 2012-08-13
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...
0
Comment
Question by:Westside2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37756024
Syntax at http://msdn.microsoft.com/en-us/library/ms177677.aspx
SELECT D, q.questionID, AVG(q.QuestionScore)
FROM Questions q
group by  q.questionID

Open in new window


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

Open in new window

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 37756986
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
0
 
LVL 9

Expert Comment

by:keyu
ID: 37771114
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
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 25

Expert Comment

by:jogos
ID: 37771154
<<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.>>
0
 
LVL 25

Expert Comment

by:jogos
ID: 37850740
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

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37850916
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.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question