postgresqlaverageconditional-aggregation

Calculating ratios in postgresql

I new to postgresql and I am trying do calculate a rate in a table like this:

class  phase
a      sold
b      stock
c      idle
d      sold

I want to calculate the total count of sold phases / total like this:

2/4 = 50%

i was trying:

with t as ( select count(class) as total_sold from table where phase='sold')

select total_sold / count(*) from t
group by total_sold

but the result is wrong. How can I do this?

Solution

  • Use AVG() aggregate function:

    SELECT 100 * AVG((phase = 'sold')::int) AS avg_sold
FROM tablename;

    The boolean expression phase = 'sold' is converted to an integer 1 for true or 0 for false and the average of these values is the ratio that you want.

    See the demo.