How to calculate ratio for many values in SQL? [duplicate]
I have a SQL Server table that contains users & their grades. For simplicity's sake, lets just say there are 2 columns - name
& grade
. So a typical row would be Name: "John Doe", Grade:"A".
I'm looking for one SQL statement that will find the percentages of all possible answers. (A, B, C, etc...) Also, is there a way to do this without defining all possible answers (open text field - users could enter 'pass/fail', 'none', etc...)
The final output I'm looking for is A: 5%, B: 15%, C: 40%, etc...
-
The most efficient (using over()).
select Grade, count(*) * 100.0 / sum(count(*)) over() from MyTable group by Grade
-
Universal (any SQL version).
select Grade, count(*) * 100.0 / (select count(*) from MyTable) from MyTable group by Grade;
-
With CTE, the least efficient.
with t(Grade, GradeCount) as ( select Grade, count(*) from MyTable group by Grade ) select Grade, GradeCount * 100.0/(select sum(GradeCount) from t) from t;
I have tested the following and this does work. The answer by gordyii was close but had the multiplication of 100 in the wrong place and had some missing parenthesis.
Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score
From MyTable
Group By Grade