Aren Aren - 5 months ago 13
SQL Question

SQL Query solutions needed for grouping answers

I'm not a specialist in SQL and I struggle to find a solution to make a SQL query for the following case. I hope someone can help me with this one!

I have a few tables where the Answer table holds the answers from the 20 Questions table. Where the answer can have a value from 1 to 5.

The questions have a types_id that will mark the questions that are related.

What I need is a query over the answer table and get the following information:

Group the question that are related ( = same types_id = same teamid and = same date ) and take the AVG from the answers that have the same types_id.

So the result can be something like:

---------------------------------------------------------
| types_id |
|teamid | date | 1 | 2 | 3 | 4 |
---------------------------------------------------------
| 12 | 2012-12-31 00:00:00 | 2 | 4 | 3 | 5 | <- holds the average answers from the related questions ( = same types_id)
---------------------------------------------------------


As an example here the Questions 1, 5, 9, 13 and 17 are related by there types_is of 1. So there are 4 groups of related questions.

Below a sample of the table structures:

Answers
table:

-----------------------------------------------------------------------------------------------------------------------------------------------------
id teamid userid date Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 timestamp done
-----------------------------------------------------------------------------------------------------------------------------------------------------
1 12 1 2012-12-31 00:00:00 1 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3 4 4 4 4 2012-12-11 08:30:27 0
2 12 2 2012-12-31 00:00:00 5 2 5 5 5 5 4 4 4 4 4 3 3 3 3 3 2 2 2 2 2012-12-11 08:50:08 0
3 12 3 2012-12-31 00:00:00 1 3 1 1 1 1 2 2 2 2 2 4 4 4 4 4 5 5 5 5 2012-12-11 08:20:37 0
1 9 11 2012-12-31 00:00:00 1 1 1 1 1 1 2 2 2 2 2 3 3 3 3 3 4 4 4 4 2012-12-11 08:30:27 0
2 9 12 2012-12-31 00:00:00 5 2 5 5 5 5 4 4 4 4 4 3 3 3 3 3 2 2 2 2 2012-12-11 08:50:08 0
3 9 23 2012-12-31 00:00:00 1 3 1 1 1 1 2 2 2 2 2 4 4 4 4 4 5 5 5 5 2012-12-11 08:20:37 0
-----------------------------------------------------------------------------------------------------------------------------------------------------


Questions
table

---------------------------------
id question types_id
---------------------------------
1 Question 1 text 1
2 Question 2 text 2
3 Question 3 text 3
4 Question 4 text 4
5 Question 5 text 1
6 Question 6 text 2
7 Question 7 text 3
8 Question 8 text 4
9 Question 9 text 1
10 Question 10 text 2
11 Question 11 text 3
12 Question 12 text 4
13 Question 13 text 1
14 Question 14 text 2
15 Question 15 text 3
16 Question 16 text 4
17 Question 17 text 1
18 Question 18 text 2
19 Question 19 text 3
20 Question 10 text 4
---------------------------------


Any help will be greatly appreciated!

Thanks Aren

Answer

First you need to unpivot the question data. I'd create view for this if you aren't prepared to store the data this way. You'll need to expand this to all 20 questions:

Create View UnpivotedAnswers As
Select
  teamid,
  date,
  1 as QuestionID,
  Q1 as Answer
From
  Answers
Union All
Select
  teamid,
  date,
  2 as QuestionID,
  Q2 as Answer
From
  Answers
Union All
Select
  teamid,
  date,
  5 as QuestionID,
  Q5 as Answer
From
  Answers

Once you have the data available in this format, getting the averages out can be done like so:

Select
  u.teamid,
  u.date,
  avg(case When q.types_id = 1 Then Answer End) as type1,
  avg(case When q.types_id = 2 Then Answer End) as type2,
  avg(case When q.types_id = 3 Then Answer End) as type3,
  avg(case When q.types_id = 4 Then Answer End) as type4,
  avg(case When q.types_id = 5 Then Answer End) as type5
From
  UnpivotedAnswers u
    Inner Join
  Questions q
    On u.QuestionID = q.id
Group By
  u.teamid,
  u.date

http://sqlfiddle.com/#!2/b1b718/1