Given the following select that will count related entities for every record
SELECT MAIN.*, SUB.app_count FROM my_table_view MAIN LEFT JOIN (SELECT MAIN2.p_key, count(DISTINCT T3.app_id) AS app_count FROM my_table MAIN2 JOIN T1 ... LEFT JOIN T2 ... LEFT JOIN T3 ... GROUP BY MAIN2.p_key) SUB ON MAIN.p_key = SUB.p_key WHERE ...
Trying to simplify it, came up with the following :
SELECT MAIN.p_key, count(DISTINCT T3.app_id) AS app_count -- select MAIN.* errors out - missing GROUP BY FROM my_table_view MAIN LEFT JOIN T1 ON ... LEFT JOIN T2 ON ... LEFT JOIN T3 ON ... WHERE ... GROUP BY MAIN.p_key
How to select all data from the
MAIN table without adding a bunch of
GROUP BYs ?
Would also appreciate performance hints.
I think this is related
Postgres GROUP BY id works on table directly, but not on identical view