postgresql - Postgres SQL: column must appear in the GROUP BY clause or be used in an aggregate function

Question

(Postgres)

I'm not clear on the following: I'm selecting various fields from STUDY_T which may be sub-selects with aggregate functions. One independent field I'm retrieving is a LOOKUP_T join, lookupStudyType.description, which is not related to any aggregate functions. But I'm getting the error

ERROR:  column "lookupstudytype.description" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:     lookupStudyType.description     AS studyTypeDescription,...

Why do I need to also group not just by s.id but also lookupStudyType.description (while the other s. groupings are not required)?

        SELECT 
            s.id                                        AS id, 
            lookupStudyType.description                 AS studyTypeDescription, 
            s.name                                      AS name, 
            s.abbreviation                              AS abbreviation, 
            s.start_date                                AS startDate, 
            s.end_date                                  AS endDate, 
            (SELECT COUNT(r.id)   
                FILTER 
                (WHERE r.status_id IN (76, 77) ))       AS  recallCount, 
            (SELECT COUNT(DISTINCT sp.id))              AS  participantCount, 
            (SELECT MAX(r.created_date) 
                FILTER 
                (WHERE r.status_id IN (76,77) ))        AS lastRecall,
            s.login_access_required                     AS loginAccessRequired, 
            s.description                               AS description, 
            s.custom_participant_exit_message           AS customParticipantExitMessage 
        FROM study_t s 
        INNER JOIN lookup_t lookupStudyType 
           ON s.study_type_id = lookupStudyType.id 
        INNER JOIN study_staff_t ss 
            ON s.id = ss.study_id 
        INNER JOIN users_t u 
            ON ss.researcher_id = u.id 
        LEFT JOIN study_participants_t sp 
            ON s.id = sp.study_id 
        LEFT JOIN recalls_t r 
            ON r.user_id = sp.user_id 
        WHERE  u.user_name = '[email protected]'
        GROUP BY 
            s.id
        ORDER  BY s.abbreviation ASC

question from:https://stackoverflow.com/questions/66065987/postgres-sql-column-must-appear-in-the-group-by-clause-or-be-used-in-an-aggrega

Answer 1

As a general rule, any column not listed in the GROUP BY clause should show up aggregated in the SELECT list.

For example s.name should show up as max(s.name) or min(s.name) since it's not present n the GROUP BY list. However, PostgreSQL implements functional dependency (a SQL Standard feature) for the GROUP BY clause, and detects that s.name is dependent in the s.id column (that is probably a PK); in short, there's a single possible value s.name for each s.id. Therefore, there's no need in PostgreSQL to aggregate this column (you can, but it's not needed).

On the flip side, for lookupStudyType.description PostgreSQL cannot determine if it's functionally dependent on s.id or not. You'll need to aggregate it as max(lookupStudyType.description) or min(lookupStudyType.description), or any other aggregation expression.

As a side note, I have rarely seen functional dependency implemented in other databases. Isn't PostgreSQL awesome? (I'm not affiliated with PostgreSQL in any way).