-1

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.