I'm writing a query which contains several aggregate columns and many ungrouped columns. I've condensed the core logic.
SELECT min(table_b.aggregate_column), table_a1_key, table_a2_key, table_a1_column FROM api.view_a JOIN api.table_b USING (table_a2_column) GROUP BY table_a1_key, table_a2_key;
table_a2 and exposes
table_a2.table_a2_key. But I get this error when executing:
ERROR: column "view_a.table_a1_column" must appear in the GROUP BY clause or be used in an aggregate function
Am I required to include other columns in the GROUP BY clause, even though they are functionally dependent on my grouped columns?
When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.