Need help calculating percentages in SQL

[aspdotnetuser]

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 [blue]CASE WHEN[/blue] 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?

[blue](CASE WHEN (answer.answerscore >= '0' AND <= '7') THEN 0-7) AS Answer1[/blue]

Or would I calculate it using a statement similar to this?

[blue](CONVERT(integer,answer.answerscore) / CONVERT(int,answer.answerscore) * 100) AS AnswerSore[/blue]

Can anyone help me with the SQL to caluclate this?

 

[jdemmi]

this worked for me...in Oracle albeit

select count(*) as total,
count(CASE when MYVALUE <=7 then 1 END) AS seven_or_less,
count(CASE when MYVALUE <=7 then 1 END)/count(*)*100 AS percentage_7_less,
count(CASE when MYVALUE >=8 then 1 END) AS eight_or_more,
count(CASE when MYVALUE >=8 then 1 END)/count(*)*100 AS percentage_8_more
from test

my tablename is test
my "answerscore" is MYVALUE

-- Jason
"It's Just Ones and Zeros

 

[RyanEK]

How about...

declare @test table (id int, score int)
insert into @test values (1, 1)
insert into @test values (2, 1)
insert into @test values (3, 1)
insert into @test values (4, 1)
insert into @test values (5, 9)
insert into @test values (6, 9)
insert into @test values (7, 9)
insert into @test values (8, 9)
insert into @test values (9, 9)
insert into @test values (10, 9)

select *, low-high as calc
from ( 
select 
sum(case when score <= 7 then 1 else 0 end) / cast(count(*) as float) * 100 as low,
sum(case when score >= 8 then 1 else 0 end) / cast(count(*) as float) * 100 as high
from @test
) as t

Ryan

 

[aspdotnetuser]

Thanks!