# How to calculate percentage with a SQL statement

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

### 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
```

### 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;`

Read more… 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.

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

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.

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!

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