Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to calculate percentage with a SQL statement

People also ask

How do you write a percentage in a database?

The percent ("P") format specifier multiplies a number by 100 and converts it to a string that represents a percentage.

How do I calculate percentage of total in MySQL?

Use Two Columns to Calculate Percentage in MySQL We calculate the percentage by dividing the SurveysCount by EmployeesCount and multiplying by 100. We use the round() function to round it for two decimal places to make it more readable.


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

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.


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!