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

Answer 1

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!

Answer 2

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

Answer 3

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

Answer 4

You can use a subselect in your from query (untested and not sure which is faster):

SELECT Grade, COUNT(*) / TotalRows
FROM (SELECT Grade, COUNT(*) As TotalRows
      FROM myTable) Grades
GROUP BY Grade, TotalRows

Or

SELECT Grade, SUM(PartialCount)
FROM (SELECT Grade, 1/COUNT(*) AS PartialCount
      FROM myTable) Grades
GROUP BY Grade

Or

SELECT Grade, GradeCount / SUM(GradeCount)
FROM (SELECT Grade, COUNT(*) As GradeCount
      FROM myTable
      GROUP BY Grade) Grades

You can also use a stored procedure (apologies for the Firebird syntax):

SELECT COUNT(*)
FROM myTable
INTO :TotalCount;

FOR SELECT Grade, COUNT(*)
FROM myTable
GROUP BY Grade
INTO :Grade, :GradeCount
DO
BEGIN
    Percent = :GradeCount / :TotalCount;
    SUSPEND;
END

Answer 5

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.

Answer 6

In any sql server version you could use a variable for the total of all grades like this:

declare @countOfAll decimal(18, 4)
select @countOfAll = COUNT(*) from Grades

select
Grade,  COUNT(*) / @countOfAll * 100
from Grades
group by Grade