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.
| Author |
Topic |
|
drew22299
Starting Member
26 Posts |
 Posted - 2009-02-19 : 04:58:13
|
| Hi,This is probably a simple calculation for SQL experts but I'm still a newbie and not sure how to do the following:I need to calculate the percentage of people who answered 0 - 7 (out of 10) and the percentage of people who answered 8 - 10 in a quiz. Then I need to subtract the percentage of people who answered 0 - 7 from the percentage of people who answered 8 - 10.The answers are stored in a table called Answer in a column called AnswerScore, I thought about using CASE WHEN but not sure if this is the correct way to calculate it, here is what I have so far, but if it's wrong can someone help?(CASE WHEN (answer.answerscore >= '0' AND <= '7') THEN 0-7) AS Answer1Or would I calculate it using a statement similar to this?(CONVERT(integer,answer.answerscore) / CONVERT(int,answer.answerscore) * 100) AS AnswerSoreCan anyone help me with the SQL to caluclate this? |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-19 : 05:22:50
|
| [code]select [Per of people who scored 0-7]=sum(case when answer.answerscore between 0 and 7 then 1.0 else 0.0 end )/count(*)*100 , [Per of people who scored 8-10]=sum(case when answer.answerscore between 8 and 10 then 1.0 else 0.0 end)/count(*) *100 , [Difference]=(sum(case when answer.answerscore between 0 and 7 then 1.0 else 0.0 end )/count(*)*100 )-(sum(case when answer.answerscore between 8 and 10 then 1.0 else 0.0 end)/count(*) *100 )from Answer [/code] |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-19 : 05:25:56
|
| You can use a function if you need this very often and for changing ranges, Like percentage of people who scored between 1&2,2&5 etc etc. If its just a one time query, needn't use a function. |
|
|
|
drew22299
Starting Member
26 Posts |
Posted - 2009-02-19 : 06:04:09
|
Thanks for your reply sakets_2000,  your query has helped me understand how to do calcuclations in SQL.Is the first part of the query required? It looks like the last line of SQL code is doing the calculation - I'm not sure what the first parts are doing in relation to the third line am I missing something?I couldn't get the query to run using your code and got an error message saying fields are invalid in the select list because they are not in a group by clause, but don't get the error when I remove your query. I have checked brackets etc but can't see why the errors are being displayed, I could add the fields to the group by clause by they wern't needed in the group by before. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-19 : 06:34:21
|
| The third line calculates the difference of the percentages. The first 2 calculates percentages for the 2 different range. You can remove the first 2 line incase you don't need them.Not sure why you are getting the error though. Can you post the exact query you are running which gives you error? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-19 : 09:26:53
|
quote:
Originally posted by drew22299
Thanks for your reply sakets_2000, your query has helped me understand how to do calcuclations in SQL.Is the first part of the query required? It looks like the last line of SQL code is doing the calculation - I'm not sure what the first parts are doing in relation to the third line am I missing something?I couldn't get the query to run using your code and got an error message saying fields are invalid in the select list because they are not in a group by clause, but don't get the error when I remove your query. I have checked brackets etc but can't see why the errors are being displayed, I could add the fields to the group by clause by they wern't needed in the group by before.
so you're trying to use some fields in select list along with aggregate functions. you cant do that. you need to use derived table in which case for aggregation and then join it with main tables to get detail data. |
|
|
|
|
|
|
|
|
Need help calculating percentages in SQL