[sql] How to calculate percentage with a SQL statement

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

I had a similar issue to this. you should be able to get the correct result multiplying by 1.0 instead of 100.See example Image attached

Select Grade, (Count(Grade)* 1.0 / (Select Count(*) From MyTable)) as Score From MyTable Group By Grade

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

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

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

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

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.

This one is working well in MS SQL. It transforms varchar to the result of two-decimal-places-limited float.

Select field1, cast(Try_convert(float,(Count(field2)* 100) / 
Try_convert(float, (Select Count(*) From table1))) as decimal(10,2)) as new_field_name 
From table1 
Group By field1, field2;

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

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;
   

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!

This is, I believe, a general solution, though I tested it using IBM Informix Dynamic Server 11.50.FC3. The following query:

SELECT grade,
       ROUND(100.0 * grade_sum / (SELECT COUNT(*) FROM grades), 2) AS pct_of_grades
    FROM (SELECT grade, COUNT(*) AS grade_sum
            FROM grades
            GROUP BY grade
         )
    ORDER BY grade;

gives the following output on the test data shown below the horizontal rule. The ROUND function may be DBMS-specific, but the rest (probably) is not. (Note that I changed 100 to 100.0 to ensure that the calculation occurs using non-integer - DECIMAL, NUMERIC - arithmetic; see the comments, and thanks to Thunder.)

grade  pct_of_grades
CHAR(1) DECIMAL(32,2)
A       32.26
B       16.13
C       12.90
D       12.90
E       9.68
F       16.13

CREATE TABLE grades
(
    id VARCHAR(10) NOT NULL,
    grade CHAR(1) NOT NULL CHECK (grade MATCHES '[ABCDEF]')
);

INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1002', 'B');
INSERT INTO grades VALUES('1003', 'F');
INSERT INTO grades VALUES('1004', 'C');
INSERT INTO grades VALUES('1005', 'D');
INSERT INTO grades VALUES('1006', 'A');
INSERT INTO grades VALUES('1007', 'F');
INSERT INTO grades VALUES('1008', 'C');
INSERT INTO grades VALUES('1009', 'A');
INSERT INTO grades VALUES('1010', 'E');
INSERT INTO grades VALUES('1001', 'A');
INSERT INTO grades VALUES('1012', 'F');
INSERT INTO grades VALUES('1013', 'D');
INSERT INTO grades VALUES('1014', 'B');
INSERT INTO grades VALUES('1015', 'E');
INSERT INTO grades VALUES('1016', 'A');
INSERT INTO grades VALUES('1017', 'F');
INSERT INTO grades VALUES('1018', 'B');
INSERT INTO grades VALUES('1019', 'C');
INSERT INTO grades VALUES('1020', 'A');
INSERT INTO grades VALUES('1021', 'A');
INSERT INTO grades VALUES('1022', 'E');
INSERT INTO grades VALUES('1023', 'D');
INSERT INTO grades VALUES('1024', 'B');
INSERT INTO grades VALUES('1025', 'A');
INSERT INTO grades VALUES('1026', 'A');
INSERT INTO grades VALUES('1027', 'D');
INSERT INTO grades VALUES('1028', 'B');
INSERT INTO grades VALUES('1029', 'A');
INSERT INTO grades VALUES('1030', 'C');
INSERT INTO grades VALUES('1031', 'F');