CODEWITHSUNDEEP
sqldatabasepostgresqlpercentage
Jacktheyeti
Jacktheyeti

Reputation: 79

How do I find the percentage between two columns in postgresql?

Two parameters in my SELECT statement are:

count(product_code.code) as codes_allocated
count(case when product_code.taken='TRUE' then product_code.taken end) as codes_claimed

I would like to add one more parameter to my select statement that takes the codes_claimed and divides it by the codes_allocated to get a percentage of the codes claimed.

I've tried many things but always get the error:

ERROR: division by zero

Query failed.

My most recent attempt used the following:

(count(case when core_isvcode.reserved='TRUE' then core_isvcode.reserved end)::decimal / count(core_isvcode.code)::decimal)*100 as Percent_redeemed`

Any help and guidance is greatly appreciated!

Upvotes: 1

Views: 2568

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think nullif() is often the simplest method:

(count(case when core_isvcode.reserved='TRUE' then core_isvcode.reserved end)::decimal / nullif(count(core_isvcode.code)::decimal))*100 as Percent_redeemed

However, I think avg() is simpler for this calculation:

avg(case when core_isvcode.reserved = 'TRUE' then 100.0
         when core_isvcode.reserved is null then NULL
         else 0.0
    end)

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

why not include a CASE to validate count(core_isvcode.code) > 0

CASE WHEN count(core_isvcode.code) > 0 THEN
   (count(case when core_isvcode.reserved='TRUE' then core_isvcode.reserved end)::decimal 
   / count(core_isvcode.code)::decimal)*100 
  ELSE NULL 
END as Percent_redeemed

Upvotes: 1

Related Questions