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

input:

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:

I expect the following output:

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

CodePudding user response：

I don't need a middle step ...

WITH
indata(id,qty,typec,converted) AS (
          SELECT 1,30,'A',true
UNION ALL SELECT 2,20,'A',false
UNION ALL SELECT 3,13,'B',false
UNION ALL SELECT 4, 7,'B',true
)
SELECT
  CAST (
    CAST(SUM(CASE WHEN CONVERTED THEN qty END)/ SUM(qty) * 100 AS INTEGER) 
  AS VARCHAR(4)
  ) || '%' AS conversion_ratio                                                                                                                                                               
, typec
FROM indata
GROUP BY typec
ORDER BY typec;
-- out  ratio | typec 
-- out ------- -------
-- out  60%   | A
-- out  35%   | B

CodePudding user response：

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