sql server - How to calculate percentage with a SQL statement

Question

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...

Answer 1

1. The most efficient (using over()).

   select Grade, count(*) * 100.0 / sum(count(*)) over()
   from MyTable
   group by Grade
   

2. Universal (any SQL version).

   select Grade, count(*) * 100.0 / (select count(*) from MyTable)
   from MyTable
   group by Grade;
   

3. 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;