Dividing two column values based on another column value

Question

I have a query that returns the following structure:

start_date | pid                 | uid                 | type        | total
2019-11-10 | 2006933595591018006 | 1812803885757105697 | recommended | 9
2019-11-10 | 2006933595591018006 | 1812803885757105697 | actual      | 3

Now I'd love to get a ratio of totals, ratio, between something that is type = actual/type = recommended. There should ever only be two results for a given set, one of each type.

Desired output:

start_date | pid                 | uid                 | ratio
2019-11-10 | 2006933595591018006 | 1812803885757105697 | 0.3333 

I assume this can be done via a group by clause and a case select or some such? I've been incapable of achieving it thus far.

Answer 1

You can do this without any CASE or GROUP BY:

edb=# create table my_table (start_date date, pid text, uid text, type text, total int);
CREATE TABLE
edb=# insert into my_table values ('2019-11-10', '2006933595591018006','1812803885757105697','recommended',9);
INSERT 0 1
edb=# insert into my_table values ('2019-11-10', '2006933595591018006','1812803885757105697','actual',3);
INSERT 0 1
edb=# SELECT a.start_date, a.pid, a.uid, a.total/r.total as ratio
        FROM my_table a
        JOIN my_table r ON (a.start_date=r.start_date AND a.pid=r.pid AND a.uid=r.uid)
       WHERE a.type = 'actual'
         AND r.type = 'recommended';
     start_date      |         pid         |         uid         |         ratio          
---------------------+---------------------+---------------------+------------------------
 2019-11-10 00:00:00 | 2006933595591018006 | 1812803885757105697 | 0.33333333333333333333
(1 row)