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

score 339 · Answer 1 · edited Aug 01 '19 at 00:53

339

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;

edited Aug 01 '19 at 00:53

Community

1
1

answered May 01 '11 at 02:50

Alex Aza

76,499
26
155
134

19

over() worked perfectly on my SQL Server 2008, I did the math to confirm. In order to round it off to 2 decimal places I used CAST(count(*) * 100.0 / sum(count(*)) over() AS DECIMAL(18, 2)). Thanks for the post! – RJB May 08 '13 at 18:19
4

In case you overflow on the 100 multiplication (e.g. _Arithmetic overflow error converting expression to data type int_), replace it with division in denominator instead: `cast((count(*) / (sum(count(*)) over() / 100)) AS DECIMAL(18, 2)) as Percentage` – Nikita R. Jan 14 '15 at 00:24
@RJB Why do you have to multiply by 100.0 and not just 100 when you're casting the output as a decimal? – AS91 Sep 07 '16 at 23:20
5

@AS91, because the cast to decimal happens AFTER the division operation. If you leave an int (100), dividing by another int will result in an int as well, which will round the result. That is why the trick is always to force a cast on the dividend before the actual division (you can either multiply by a literal decimal like 1.0 or cast/convert) – luiggig Mar 02 '17 at 08:37
Option 1 with `over()` works great on Postgresql 10 – James Daily Sep 17 '19 at 17:31

score 275 · Accepted Answer · edited Dec 01 '09 at 17:41

275

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

edited Dec 01 '09 at 17:41

Kip

107,154
87
232
265

answered Apr 21 '09 at 12:54

Jason

17,276
23
73
114

22

this gives result in integers .sum of results is not equal to 100. – Thunder Jan 26 '10 at 10:41
10

Not the most efficient as the table will be scanned twice. Also the query will not look that simple if there is more than one table referenced. – Alex Aza May 19 '11 at 21:15
14

@Thunder you can change 100 to 100.0 for decimal values. – Joseph Feb 25 '14 at 21:20
Can someone explain why the mathematical syntax of the SQL query isn't what you'd expect to do normally? For example normal I would have divided by total then times by 100? Genuinely curious about this from a logical stand point. – JoeTomks Mar 22 '16 at 13:17
4

@Digitalsa1nt (100 * 2) / 4 = 50, (2/4) * 100 = 50 as long as the enumerator is is the part being multiplied. Due to precedence of SQL statements it will be the same. however, due to data types if using 100 you can still get the result rounded to 0 decimals you desire for the % where as if you put it after the division operation you would have to make sure that you cast to a data type that can handle the decimal places otherwise you will end up with 100 or 0 and never an actual percentage – Matt Sep 20 '16 at 23:42
@Matt Thanks for the response, that makes sense, and I think the part about data types having to support decimals is what originally threw me off, because I wasn't contextualising the constraints of the data types in my thinking, so in my head it was all a bit strange. – JoeTomks Sep 21 '16 at 11:58
the solution it's good but you have to take into account that the divisor could be 0 if the table is empty and that would throw an error – Quethzel Diaz Oct 26 '18 at 16:33
Somehow this solution didn't give the sum as 100 in the end. I tried changing to decimal values as well. Using @john-gibb 's answer returns 100. Not sure why – Pirate X Nov 14 '18 at 11:54

score 49 · Answer 3 · edited May 16 '13 at 15:42

49

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.

edited May 16 '13 at 15:42

Andriy M

76,112
17
94
154

answered May 01 '09 at 03:01

John Gibb

10,603
2
37
48

+1. This is great. It can also be used if in place of 'table' there is a select statement. – mr_georg Sep 01 '09 at 20:26
1

It uses a spool in `tempdb` which is the work table. The logical reads seem higher [but they are counted differently than normal](http://stackoverflow.com/a/5194902/73226) – Martin Smith Feb 11 '12 at 16:50
2

Actually, the `COUNT(*) OVER ()` in your query would return a completely unrelated figure (specifically, the number of rows of the *grouped* result set). You should use `SUM(COUNT(*)) OVER ()` instead. – Andriy M May 01 '13 at 17:02

Fandango68 · Answer 4 · 2021-03-02T01:23:18.110

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 1 decimal, whereas 100 on it's own will round up the result to the nearest whole number, even with the ROUND(...,2) function!

edited Mar 02 '21 at 01:23

answered May 24 '16 at 02:31

Fandango68

4,461
4
39
74

Jhonny D. Cano -Leftware- · Answer 5 · 2009-04-21T00:31:17.870

11

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

edited Apr 21 '09 at 00:31

answered Apr 21 '09 at 00:20

Jhonny D. Cano -Leftware-

17,663
14
81
103

1

Of course, this only gives the percentages for grade codes present in the table, not for those that could be present and aren't. But without a definitive list of the relevant (valid) grade codes, you can't do better. Hence the +1 from me. – Jonathan Leffler Apr 21 '09 at 01:29
1

The hidden gem for me was you commented out CONVERT. – Chris Catignani Mar 13 '20 at 15:52

Jeremy · Answer 6 · 2009-04-21T01:52:49.360

11

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.

edited Apr 21 '09 at 01:52

answered Apr 21 '09 at 00:27

Jeremy

6,580
2
25
33

2

Since you have an aggregate ('sum(CountofGrade)') in the outer select, don't you need a group by clause in it too? And in standard SQL, I think you could use '/ (SELECT COUNT(*) FROM Grades)' to get the grand total. – Jonathan Leffler Apr 21 '09 at 01:32
IBM Informix Dynamic Server doesn't like the naked SUM in the select-list (though it gives a somewhat less-than-helpful message when it complains). As noted in my answer and prior comment, using a full sub-select expression in the select-list does work in IDS. – Jonathan Leffler Apr 21 '09 at 03:29
This is also better because one can apply complex where to inner query. – mvmn Nov 09 '16 at 12:51

score 7 · Answer 7 · edited May 01 '13 at 09:48

7

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

edited May 01 '13 at 09:48

Tony

9,672
3
47
75

answered Apr 21 '09 at 00:50

GordyII

7,067
16
51
69

1

this works, but the answers all come back as 0 - do I need to do some sort of number formatting or conversion to see the proper answer? – Alex Apr 21 '09 at 01:37
1

Select Grade, round(Count(grade) * 100.0 / ((Select Count(grade) From grades) * 1.0) ,2) From grades Group By Grade for adding a round function in sql-server returend eg : 21.56000000000 – Thunder Jan 26 '10 at 10:54

Jonathan Leffler · Answer 8 · 2010-01-27T05:48:35.173

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');

@Thunder: interesting; what happens if you change, say, the 100 to 100.00? — Jonathan Leffler, Jan 26 '10 at 14:50

score 4 · Answer 9 · edited Sep 05 '12 at 09:45

4

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

edited Sep 05 '12 at 09:45

j0k

22,600
28
79
90

answered Sep 05 '12 at 09:39

Aakashi

41
1

score 3 · Answer 10 · answered Apr 21 '09 at 00:28

3

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

answered Apr 21 '09 at 00:28

Steve Willcock

26,111
4
43
40

score 3 · Answer 11 · answered Apr 21 '09 at 00:34

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

score 1 · Answer 12 · answered Apr 07 '20 at 08:20

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;

score 0 · Answer 13 · edited Feb 16 '20 at 00:10

0

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 See reference image attached

edited Feb 16 '20 at 00:10

Marcello B.

4,177
11
45
65

answered Feb 15 '20 at 23:51

Gabriel E

1
1

Please do not share information as images unless absolutely necessary. See: https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors. – AMC Feb 16 '20 at 01:05

How to calculate percentage with a SQL statement

13 Answers13

Linked

Related