0

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?

  • Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… – a_horse_with_no_name Jun 24 at 5:41
  • Thanks for the tip. In our case 99% of interaction with this database is managed by Sequelize (a javascript ORM), so camel-casing in our column names allows us to keep the casing in our codebase consistent, and the ORM takes care of quoting the identifiers. – ChrisJ Jun 24 at 5:56
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"
;
| improve this answer | |

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.