Calculating Percentages in Postgres (and Redshift!)

This is a quick one —

There are a few ways to cast integers to decimals with SQL, but I prefer one specific method — using the CAST() function.

It’s easy to just multiply your integers by 1.0 to cast it as a decimal type before you perform an operation, but if you’re doing analytics work or writing queries that you’d like other people to be able to decipher, I think the CAST() is much more expressive.

You can use it like so:

CAST(expression AS TYPE)

If I’m calculating a percentage of two integers with Amazon Redshift (which is just a Postgres variant), I like to so in my SELECT statement with the CAST() function:

(cast(value1 as decimal) / 
cast(value2 as decimal) as value_percentage

You can also cast the output of aggregates like this:


SELECT (cast(count(column_in_question) as decimal) /
cast(count(*) as decimal) as value_percentage

FROM table
WHERE column_in_question IS NOT NULL

This will give you the percentage of all rows that have a value in the column in question.