# Dividing two column values based on another column value

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.

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)
``````
• Works like a charm. – tr3online Nov 19 at 0:05