SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE assets ( id serial, name text, owner text, PRIMARY KEY(id) ); CREATE TABLE users ( id serial, username text, ref1 text, ref2 text, PRIMARY KEY(id) ); INSERT INTO assets (name, owner) VALUES ('#1', 'a'), ('#2', 'b'), ('#3', 'c'), ('#4', 'a'), ('#5', 'c'), ('#6', 'd'), ('#7', 'e'), ('#8', 'd'), ('#9', 'a'), ('#10', 'a'), ('#11', 'z') ; INSERT INTO users (username, ref1, ref2) VALUES ('bobo', 'a', 'd'), ('toto', 'b', 'e'), ('momo', 'c', 'd'), ('lolo', 'a', 'f'), ('popo', 'c', 'f'); -- For result comparison CREATE TABLE results ( ids int[], usernames text[], refs1 text[], refs2 text[], asset_ids int[], asset_count int ); INSERT INTO results (ids, usernames, refs1, refs2, asset_ids, asset_count) VALUES (array[1,3,4,5], array['bobo', 'momo', 'lolo', 'popo'], array['a', 'c'], array['d', 'f'], array[1,3,4,5,6,8], 6), (array[2], array['toto'], array['b'], array['e'], array[2,7], 2); WITH agg1 as ( SELECT string_agg(distinct users.id::text, ',') id, string_agg(distinct username, ',') username, string_agg(distinct ref2, ',') ref2, string_agg(distinct assets.id::text, ',') assets_id, sum(assets_count) assets_count, ref1 FROM users JOIN ( SELECT string_agg(id::text, ',') id, count(*) assets_count, owner FROM assets GROUP BY owner ) assets ON assets.owner in (ref1, ref2) GROUP BY ref1 ) SELECT string_to_array(string_agg(id::text, ','),',') ids, string_to_array(string_agg(username, ','),',') usernames, string_to_array(string_agg(ref1, ','),',') refs1, string_to_array(ref2,',') refs2, string_to_array(string_agg(assets_id, ','),',') assets_id, min(assets_count) assets_count FROM agg1 GROUP BY agg1.ref2 ; -- For comparison SELECT * FROM results;