[SOLVED] Average using SQL Cross Tab

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 2005 Forums

Transact-SQL (2005)

[SOLVED] Average using SQL Cross Tab

Author

Topic

dfs3000my
Starting Member

9 Posts

Posted - 2008-12-23 : 20:20:20

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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 14:15:47

Remove Question and Category_id from Group By then you should be good to go.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 02:09:18

also casting to int will always give you integer results alone, if you want decimal values also dont cast it to int

dfs3000my
Starting Member

9 Posts

Posted - 2008-12-29 : 20:20:57

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:38:42

thats because you'rew casting it to int try this one


... 
AVG(CASE question.category_id WHEN 1 THEN SUBSTRING(answer.answer,4,1)*1.0 ELSE 0.0 END) AS [Average Benefits Rating],
AVG(CASE question.category_id WHEN 2 THEN SUBSTRING(answer.answer,4,1)*1.0 ELSE 0.0 END) AS [Average Facilities Rating]
....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:39:53

also see this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

dfs3000my
Starting Member

9 Posts

Posted - 2008-12-30 : 03:04:33

Hi Visakh,

Thanks for your help. I've tried removing the casting but it still does not work as the AVG() is only accurate for CASE 1. For CASE 2, it has a bit of a problem determining the total number of items that it needs to be divided with. The first image below shows the coding with the removed integer casting.

The problem that I have is that I am not grouping it by their categories as the average needs to be calculated based on their respective categories. I've tried grouping them back via their category_id and the average that I have got is accurate. This can be seen in the second image below.

However, I need my output to be in this form:-

Year | Month | Benefits Rating | Facilities Rating
-----------------------------------------------------
2007 | December | 2.68 | 5
2008 | November | 3.10 | 5
2008 | October | 2.94 | 5
2008 | September| 2.94 | 2.5
2009 | December | 3.36 | 3.5
-----------------------------------------------------

and not like this:-

Year | Month | Benefits Rating | Facilities Rating
-----------------------------------------------------
2007 | December | 2.68 | 0
2007 | December | 0 | 5
2008 | November | 3.10 | 0
2008 | November | 0 | 5
2008 | October | 2.94 | 0
2008 | October | 0 | 5
2008 | September| 2.94 | 0
2008 | September| 0 | 2.5
2009 | December | 3.36 | 0
2009 | December | 0 | 3.5
-----------------------------------------------------

I hope I am not being too confusing :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-30 : 03:28:07

[code]SELECT DATEPART(YEAR, q.attempted),
DATENAME(MONTH, q.attempted),
AVG(CASE WHEN q.category_id = 1 THEN 1.0E * SUBSTRING(a.answer, 4, 1) ELSE NULL END) AS [Average Benefits Rating],
AVG(CASE WHEN q.category_id = 2 THEN 1.0E * SUBSTRING(a.answer, 4, 1) ELSE NULL END) AS [Average Facilities Rating]
FROM questionnaire AS q
INNER JOIN employee AS e ON e.employee_number = q.employee_id
INNER JOIN answer AS a ON a.employee_id = e.id
INNER JOIN question AS w ON w.id = a.question_id
WHERE q.attempted IS NOT NULL
AND w.question_type = 1
AND a.answer <> 'ansNA'
GROUP BY DATEPART(YEAR, q.attempted),
DATENAME(MONTH, q.attempted)[/code]

E 12°55'05.63"
N 56°04'39.26"

dfs3000my
Starting Member

9 Posts

Posted - 2008-12-30 : 04:04:25

Perfect Peso!

Thanks a lot! But, mind explaining a bit? What's the difference when I put "CASE WHEN q.category_id = 1" AND "CASE q.category_id WHEN 1"? Or is it because of the "ELSE NULL END" that is making the code work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 04:08:32

quote:
Originally posted by dfs3000my

Perfect Peso!

Thanks a lot! But, mind explaining a bit? What's the difference when I put "CASE WHEN q.category_id = 1" AND "CASE q.category_id WHEN 1"? Or is it because of the "ELSE NULL END" that is making the code work?

its because of ELSE NULL part its working. Actually i missed spotting it before i posted. The NULL will cause all records without satisfying condition q.category_id = 1 to be ignored. In the former case it will still count it as 0 value and give average as 0. that was reason for discrepancy.

dfs3000my
Starting Member

9 Posts

Posted - 2008-12-30 : 04:35:59

Ah now I understand. Thank you so much for your help. Do I need to mark this post as SOLVED or something like that? If I do, where can I do it? Not too familiar with this forum though ;)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 04:42:47

quote:
Originally posted by dfs3000my

Ah now I understand. Thank you so much for your help. Do I need to mark this post as SOLVED or something like that? If I do, where can I do it? Not too familiar with this forum though ;)

Not mandatory. However you can edit the heading of this thread and append [SOLVED] if you want to.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources