Need help in getting Average using SQL Cross Tab
 

Hi all,

I have managed to get my average using cross tabs but only if I have grouped it by their categories. If I remove the GROUP BY clause, the cross tabbing doesn't seem to work. Can anyone help me? I would need to get something like:-

Year | Month | Benefits Rating | Facilities Rating

-----------------------------------------------------

2007 | December | 2 | 3

2008 | November | 3 | 3

2008 | October  | 2 | 1

2008 | September| 2 | 2

Thanks

p/s: example is shown as an image below.

Attachment 8641

Re: Need help in getting Average using SQL Cross Tab
 

Make it subquery like below:

select [Year], [Month], sum([Benefits Rating]) as [Benefits Rating], sum([Facilities Rating]) as [Facilities Rating]

  from (...... your origin query ......) X

  group by [Year], [Month]

Re: Need help in getting Average using SQL Cross Tab
 

Hi,

I've tried removing the question.category_id from the GROUP BY clause but AVG() doesn't seem to work. It works perfectly fine for SUM() but not AVG().

Below is how the output looks without the question.category_id in the GROUP BY clause.

Attachment 8701

Re: Need help in getting Average using SQL Cross Tab
 

I have managed to find a solution to this problem

SELECT     DATEPART(year, q.attempted_date) AS Year, DATENAME(MM, q.attempted_date) AS Month, 

                      AVG(CASE WHEN w.category_id = 1 THEN CAST(SUBSTRING(a.answer, 4, 1) AS int) ELSE NULL END) AS [Average Benefits Rating], 

                      AVG(CASE WHEN w.category_id = 2 THEN CAST(SUBSTRING(a.answer, 4, 1) AS int) ELSE NULL END) AS [Average Facilities Rating]

FROM         questionnaire AS q INNER JOIN

                      employee AS e ON q.employee_id = e.employee_number INNER JOIN

                      answer AS a ON e.id = a.employee_id INNER JOIN

                      question AS w ON a.question_id = w.id

WHERE     (w.question_type = 1) AND (a.answer <> 'ansNA') AND (q.attempted_date IS NOT NULL)

GROUP BY DATEPART(year, q.attempted_date), DATENAME(MM, q.attempted_date)

ORDER BY Year, Month DESC