Selecting non-aggregate column on 3 table inner join

Question

I have 3 tables in a standard parent/child kind of "belongs to" relationship in postgres:

donations --(belong to)--> campaigns --(belong to)--> organisations

Each table has an id column as a primary key, that's used as a foreign key by the child table

If I'm doing a SELECT with a GROUP BY on the table, I can't select a column from the related organisations table without an aggregate function, even though there can only be one joining row. I've always worked around this by using MAX() since I know there's only one row for the join, eg

SELECT 
campaigns.name,
MAX(organisations.name),
SUM(amount)
FROM donations
JOIN campaigns ON campaigns.id = donations."campaignId"
JOIN organisations ON organisations.id = campaigns."organisationId"
GROUP BY campaigns.id;

but this feels like quite hacky. Is there a more correct way to do this?

Answer 1

This can be solved by aggregating first and joining to the result of the aggregation:

SELECT c.name as campaign_name,
       o.name as organization_name
       d.total_amount
FROM (
  select "campaignId", sum(amount) as total_amount
  from donations
  group by "campaignId"
) d   
  JOIN campaigns c ON c.id = d."campaignId"
  JOIN organisations o ON o.id = c."organisationId"
;