How Can I Calculate Grade Percentages in SQL Without Predefining All Possible Grades?

Calculating Percentage in a SQL Statement

Question:

A SQL Server table contains user names and their grades. How can a single SQL statement calculate the percentages of all possible grade values (e.g., A, B, C)? Additionally, is it possible to achieve this without explicitly defining all possible grades, considering that users may enter open-ended responses such as 'pass/fail' or 'none'?

Answer:

Option 1: Using over()

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

This solution is the most efficient and utilizes the over() function.

Option 2: Universal (Any SQL Version)

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

This option works for any SQL version but may be less efficient than the previous one.

Option 3: With CTE (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;

While this method provides a flexible solution, it is the least efficient.

The above is the detailed content of How Can I Calculate Grade Percentages in SQL Without Predefining All Possible Grades?. For more information, please follow other related articles on the PHP Chinese website!