[RESOLVED] Help With Query - Join Or Subselect?

[scriderpsd]

I have a table of survey responses for helpdesk tickets and I need to output the data to give the averages for each survey question (as well as the average of all quesitons) broken down by support staff.

My survey response table (ost_survey_response) looks like this.

+-------------+-----------+----------+----------+
| response_id | ticket_id | quest_id |  rating  |
+-------------+-----------+----------+----------+
|     11      |   1936    |     4    |    4     |
|     12      |   1943    |     3    |    5     |
|     13      |   1949    |     3    |    5     |
|     14      |   1870    |     3    |    5     |
|     15      |   1901    |     3    |    4     |
|     16      |   1452    |     1    |    4     |
|     17      |   1860    |     4    |    4     |
|     18      |   1953    |     2    |    5     |
+-------------+-----------+----------+----------+

I have the following query

SELECT ost_staff.firstname AS FN, ost_staff.lastname AS LN,
ROUND(AVG(ost_survey_response.rating),2) as "AVG All"

FROM ost_staff, ost_ticket, ost_survey_response, ost_survey_questions
WHERE ost_staff.staff_id=ost_ticket.staff_id
AND ost_survey_response.ticket_id=ost_ticket.ticket_id
AND ost_survey_questions.quest_id=ost_survey_response.quest_id

GROUP BY ost_staff.staff_id 
ORDER BY ost_staff.lastname

That is giving me the following result

+------+---------------+----------+
|  FN  |    LN         | AVG All  |
+------+---------------+----------+
| Chris| Brown         | 4.33     |
| Kim  | Harris        | 4.67     |
| Rob  | Pyke          | 4.0      |
+------+---------------+----------+

What I need to output is something like this...

+------+---------------+----------+----------+----------+----------+----------+----------+
|  FN  |    LN         |    Q1    |    Q2    |    Q3    |    Q4    |    Q5    | AVG All  |
+------+---------------+----------+----------+----------+----------+----------+----------+
| Chris| Brown         |  Avg Q1  |  Avg Q2  |  Avg Q3  |  Avg Q4  |  Avg Q5  | 4.33     |
| Kim  | Harris        |  Avg Q1  |  Avg Q2  |  Avg Q3  |  Avg Q4  |  Avg Q5  | 4.67     |
| Rob  | Pyke          |  Avg Q1  |  Avg Q2  |  Avg Q3  |  Avg Q4  |  Avg Q5  | 4.0      |
+------+---------------+----------+----------+----------+----------+----------+----------+

Okay, so my question is how do I get the averages for each question broken down by staff? I'm not real solid with joins other than the simple ones in the earlier query.

Any help is very much appreciated.

Thanks,

Scott

[Lars_Berg]

select ost_staff.firstname as FN, 
       ost_staff.lastname as LN,
       round(avg(case when quest_id = 1 then ost_survey_response.rating end),2) as "AVG Q1",
       round(avg(case when quest_id = 2 then ost_survey_response.rating end),2) as "AVG Q2",
       round(avg(case when quest_id = 3 then ost_survey_response.rating end),2) as "AVG Q3",
       round(avg(case when quest_id = 4 then ost_survey_response.rating end),2) as "AVG Q4",
       round(avg(case when quest_id = 5 then ost_survey_response.rating end),2) as "AVG Q5",
       round(avg(ost_survey_response.rating),2) as "AVG All"
  from ost_staff, 
       ost_ticket, 
       ost_survey_response, 
       ost_survey_questions
 where ost_staff.staff_id = ost_ticket.staff_id
   and ost_survey_response.ticket_id = ost_ticket.ticket_id
   and ost_survey_questions.quest_id = ost_survey_response.quest_id
 group
    by ost_staff.staff_id 
 order
    by ost_staff.lastname

[scriderpsd]

Awesome! Thanks Lars! That is exactly what I was trying to accomplish. I really appreciate your help.

Scott