Categories
Database Development

Select records and related entity count

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *