Grouping survey results problem

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

SQL Server 2000 Forums

SQL Server Development (2000)

Grouping survey results problem

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-12-16 : 20:32:15

I have a survey where users from several divisions are scoring departments of a business. They can give them a score from 1-5 or 9 if they cannot rate the department.

The main tables look like this:

Users
------
UserID int
division varchar(200) -- This if from a drop down so always consistent

Results
--------
resultID int
userID int
question int
division int
answer int

Divisions
----------
divisionID int
divisionName varchar(50)

Here is my query so far:

 
SELECT 
		AVG(answer) as Average_Score,
		u.division,
		d.divisionName as Department
	FROM
		Results r
		INNER JOIN Users u ON u.userID = r.userID
		INNER JOIN Divisions d ON d.divisionID = r.division
	WHERE
		r.question = 1
		AND r.answer <> 9
	GROUP BY
		d.divisionName,
		u.division
	ORDER BY
		u.division

Which gives me results such as:


Average_Score  |   division        |   Department
-------------------------------------------------- 
3              | Customer Service  | Catering
4              | Customer Service  | Reception/Switch
2              | Marketing         | Catering
5              | Marketing         | Reception/Switch

This is what I need, but I also need one column that tells me how many people gave a score of 9 for each department grouped by each division. So just a final column on the above table with the number of people who answered 9, but I don't know how to do it!

And sorry about the crap field names.

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-16 : 22:22:59

Search the site for Pivot tables. There are a couple of good examples on how to do what you want.

Use a CASE statement on 1-5, 9.

Should do the trick.

slow down to move faster...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-16 : 22:38:39

Give this a try:


SELECT 
		SUM(CASE WHEN r.answer <> 9 THEN r.answer ELSE 0 END)/SUM(CASE WHEN r.answer <> 9 THEN 1 ELSE 0 END) as Average_Score,
		u.division,
		d.divisionName as Department,
                SUM(CASE WHEN r.answer = 9 THEN 1 ELSE 0 END) as NumberOf9Votes
	FROM
		Results r
		INNER JOIN Users u ON u.userID = r.userID
		INNER JOIN Divisions d ON d.divisionID = r.division
	WHERE
		r.question = 1
	GROUP BY
		d.divisionName,
		u.division
	ORDER BY
		u.division

- Jeff

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-12-16 : 22:49:09

Thanks jsmith8858, but I get an error from this. The problem is that there is a chance (slight) that everyone from the same division could answer 9 for a department on a particlar question, so I get a "Divide by zero error encountered" error.

Thanks, I'll look for the stuff on pivot tables.

Tim
Starting Member

392 Posts

Posted - 2002-12-17 : 00:12:56

you would get a better response if you posted code to create your tables and populate them with sample data, together with expected results.

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-12-17 : 01:12:33

Yeah, good thinking Tim, I will try that next time.

I got it going, basically I did it the same way jsmith8858 has, but instead of doing the average in the query, I just returned the 2 values separately, and created the average from them.

Thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources