How to calculate percentage with a SQL statement

Question

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

Alex

2013/03/06

Popular Answer

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;

Alex Aza

2019/08/01

Read less…

Instead of using a separate CTE to get the total, you can use a window function without the "partition by" clause.

If you are using:

count(*)

to get the count for a group, you can use:

sum(count(*)) over ()

to get the total count.

For example:

select Grade, 100. * count(*) / sum(count(*)) over ()
from table
group by Grade;

It tends to be faster in my experience, but I think it might internally use a temp table in some cases (I've seen "Worktable" when running with "set statistics io on").

EDIT: I'm not sure if my example query is what you are looking for, I was just illustrating how the windowing functions work.

John Gibb

2013/05/16

You have to calculate the total of grades If it is SQL 2005 you can use CTE

    WITH Tot(Total) (
    SELECT COUNT(*) FROM table
    )
    SELECT Grade, COUNT(*) / Total * 100
--, CONVERT(VARCHAR, COUNT(*) / Total * 100) + '%'  -- With percentage sign
--, CONVERT(VARCHAR, ROUND(COUNT(*) / Total * 100, -2)) + '%'  -- With Round
    FROM table
    GROUP BY Grade

Jhonny D. Cano -Leftware-

2009/04/21

You need to group on the grade field. This query should give you what your looking for in pretty much any database.

    Select Grade, CountofGrade / sum(CountofGrade) *100 
    from
    (
    Select Grade, Count(*) as CountofGrade
    From Grades
    Group By Grade) as sub
    Group by Grade

You should specify the system you're using.

Jeremy

2009/04/21

I simply use this when ever I need to work out a percentage..

ROUND(CAST((Numerator * 100.0 / Denominator) AS FLOAT), 2) AS Percentage

Note that 100.0 returns decimals, whereas 100 on it's own will round up the result to the nearest whole number, even with the ROUND() function!

Fandango68

2016/05/24

The following should work

ID - Key
Grade - A,B,C,D...

EDIT: Moved the * 100 and added the 1.0 to ensure that it doesn't do integer division

Select 
   Grade, Count(ID) * 100.0 / ((Select Count(ID) From MyTable) * 1.0)
From MyTable
Group By Grade

GordyII

2013/05/01

Jason · Accepted Answer · 12/1/2009 5:41:30 PM

Accepted Answer

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

Jason

2009/12/01