Sql – How to calculate percentage with a SQL statement

sqlsql-servertsql

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…

Best Solution

  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;