Help with an SQL Query - SQL Server Forums

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

Other Forums

MS Access

Help with an SQL Query

Author

Topic

Avalon
Starting Member

2 Posts

Posted - 2007-12-04 : 12:59:52

I have the following tables:

What I want to find out is the average LevelID for each QuestionID. The query I have so far is:

SELECT Questions.QuestionID, Questions.Quesion, Levels.LevelID, Levels.LevelName, Avg(Levels.LevelID) AS AvgOfLevelID
FROM Questions INNER JOIN (Survey INNER JOIN (Levels INNER JOIN Rating ON Levels.LevelID = Rating.LevelID) ON Survey.SurveyID = Rating.SurveyID) ON Questions.QuestionID = Rating.QuestionID
GROUP BY Questions.QuestionID, Questions.Quesion, Levels.LevelID, Levels.LevelName;
(generated from Access) but it's not working correctly. Can someone help?

If there are say, 8 questions, I want the query to only return 8 numbers.

tacket
Starting Member

47 Posts

Posted - 2007-12-04 : 17:28:27

What's the data look like in Levels? When you say average, that basically means you take the total of all the levels (which I have no idea what column that is in the levels table) divided by the the total number of questions, so there had better be another column in levels which is an integer indicating the level. Let's assume there is then you would just do something like this:

select surveyID,questionID,question,sum(level) / count(*)
from rating a
inner join questions b
on b.questionID = a.questionID
inner join levels c
on c.levelID = a.levelID
inner join suver d
on d.surveyID = a.surveyID
group by surveyID,questionID,question