Postgres conversion rate

I want to get conversion rate in postgresql. My data look like below:

input:

id	count	type	converted
1	30	A	true
2	20	A	false
3	13	B	false
4	7	B	true

As first step, I would like to get a sum of counts for each type with associated count field. I tried with different variations of SUM() but couldn’t get it. This middle step would look like:

id	count	type	converted	sum
1	30	A	true	50
2	7	B	true	20

I expect the following output:

id	conversion_rate	type
1	60%	A
2	35%	B

but my problem is to write proper SQL to get to the middle step.

Answer

I think for the middle step you’d rather need something like:

create temporary table input(
  id int primary key generated always as identity,
  count int,
  type text,
  converted boolean
);
insert into input(count, type, converted) values
  (30,'A',true),
  (20,'A',false),
  (13,'B',false),
  (7,'B',true);

select
  sum(case when converted then count else 0 end) as count_converted,
  sum(count) as count,
  type
from input
group by type;

So your target query would just be:

select
  (
    sum(case when converted then count else 0 end)::float
    /
    sum(count)::float
  )*100 as conversion_rate,
  type
from input
group by type;

Result:

 conversion_rate | type 
-----------------+------
              35 | B
              60 | A