CREATE TABLE bought_products (id int, name text, customer_id int, product_id int, foo int, bar int, blarg int); INSERT INTO bought_products (id, name , customer_id, product_id, foo, bar, blarg) VALUES (1, 'test', 123, 321, NULL, '1' , NULL), (2, 'tast', 123, 231, '5' , NULL, '20'), (3, 'tost', 456, 321, NULL, '3' , NULL); CREATE TABLE Price (id int, item text, product_id int, cost numeric); INSERT INTO Price VALUES (1, 'Foo', 321, 10), (2, 'Bar', 321, 5), (3, 'BASE', 321, 100), (4, 'BASE', 231, 50), (5, 'Blarg', 231, 0.5), (6, 'Foo', 231, 1);

SELECT customer_id AS customer , string_agg(DISTINCT product_id::text, ', ') AS products , sum(cost * CASE p.item WHEN 'BASE' THEN 1 WHEN 'Foo' THEN b.foo WHEN 'Bar' THEN b.bar WHEN 'Blarg' THEN b.blarg END) AS revenue FROM bought_products b LEFT JOIN price p USING (product_id) -- WHERE p.item = ANY ('{BASE,Foo,Bar,Blarg}') -- optional GROUP BY 1;