07-31-2023, 09:45 AM
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;
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;