Calculate percentage between two columns in SQL Query as another column

Question

I have a table with two columns, number of maximum number of places (capacity) and number of places available (availablePlaces)

I want to calculate the availablePlaces as a percentage of the capacity.

availablePlaces    capacity
1                  20
5                  18
4                  15

Desired Result:

availablePlaces    capacity  Percent
1                  20        5.0
5                  18        27.8
4                  15        26.7

Any ideas of a SELECT SQL query that will allow me to do this?

Giorgos Betsos · Accepted Answer · 2016-04-10T15:12:49.460

59

Try this:

SELECT availablePlaces, capacity, 
       ROUND(availablePlaces * 100.0 / capacity, 1) AS Percent
FROM mytable

You have to multiply by 100.0 instead of 100, so as to avoid integer division. Also, you have to use ROUND to round to the first decimal digit.

Demo here

edited Apr 10 '16 at 15:12

answered Apr 10 '16 at 15:07

Giorgos Betsos

71,379
9
63
98

@missdevops Glad I was able to help. Please mark this, or any other, answer as accepted if it helped you solve your problem. – Giorgos Betsos Apr 10 '16 at 15:12
Perfect, just what I needed! Had originally done this without ROUND() and without dividing by 100.0 and was getting only zeroes and ones. Much appreciated. :-) – allardbrain Nov 06 '17 at 02:01
Your comment on multiplying by 100.0 instead of 100 was the key in my case. Thanks. – Jorge Jan 17 '18 at 16:51
For SQL Server in SSMS, the output will show a bunch of trailing zeros because it casts this to a decimal. You have to convert it to a float to remove trailing zeros: ROUND(CAST(availablePlaces * 100.0 / capacity as FLOAT), 1) – sean Oct 22 '18 at 15:08
What if you don't tables but want to use 2 selects ? Like I have a complex select that would make avaialable places X digit, and another selects that create capacity as Y digit. how do I use it? – Freedo Apr 27 '22 at 08:29

score 1 · Answer 2 · edited Apr 10 '16 at 20:59

1

The following SQL query will do this for you:

SELECT availablePlaces, capacity, (availablePlaces/capacity) as Percent 
from table_name;

edited Apr 10 '16 at 20:59

jotik

17,044
13
58
123

answered Apr 10 '16 at 15:04

bryan.blackbee

1,934
4
32
46

This will do integer division and return either 0 or 1. – imoatama Sep 15 '20 at 09:38

score 0 · Answer 3 · answered Oct 20 '20 at 15:48

Why not use a number formatting function such as format_number (or an equivalent one in your database) to format a double as a percentage? This example is generalized. The returned value is a string.

WITH t 
AS 
( 
    SELECT count(*) AS num_rows, count(foo) as num_foo
    FROM mytable
)

SELECT *, format_number(num_foo/num_rows, '#.#%') AS pct_grade_rows
FROM t

This avoids the use of round and multiplying the numerator by 100.

Calculate percentage between two columns in SQL Query as another column

3 Answers3