Given the following select that will count related entities for every record

SELECT MAIN.*, SUB.app_count
FROM my_table_view MAIN
                       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

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 ...

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

  • Remove GROUP BY at all. Use window version of COUNT(). Add DISTINCT for to remove duplicates from output rowset. PS. This is NOT a primary key - you must look at query data source, not at a particular table in it.
    – Akina
    Jun 30 '20 at 5:20
  • @Akina do you mind writing a response ?
    – Bax
    Jul 1 '20 at 17:30

If p_key is the primary key of my_table, it should not be necessary to add the other columns, unless you want the statement to comply with the SQL standard.

  • p_key is the primary key but I select from a view
    – Bax
    Jun 30 '20 at 23:13
  • Then add all the columns to the GROUP BY clause. It is the safe and standard way. Jul 1 '20 at 6:07

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.