Need help in getting Average using SQL Cross Tab
Please support our MS SQL advertiser: Programming Forums
Thread Solved
 |
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:-
Thanks
p/s: example is shown as an image below.
sql.PNG
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.
sql.PNG
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]
Hence Wijaya
www.ex-Soft.tk
www.ex-Soft.tk
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
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
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 |
Other Threads in the MS SQL Forum
- Previous Thread: Debugger for PL/SQL in sql server
- Next Thread: Crazy CrossTab problems
•
•
•
•
Views: 874 | Replies: 3 | Currently Viewing: 1 (0 members and 1 guests)
Linear Mode
