Postgresql – Selecting non-aggregate column on 3 table inner join

aggregategroup byjoin;postgresql

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?

Best Answer

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"
;

Related Solutions

Postgresql – Error “column does not exist” in a SELECT with JOIN and GROUP BY query

You probably want something like this:

SELECT h_v_charges.*, 
       last_v.last_version
FROM hist_versions_charges h_v_charges 
  JOIN (select proj_charge_id, 
               max(version) as last_version
        from hist_versions_charges 
        where proj_sous_projet_id = 2  
        group by proj_charge_id
  ) last_v  
  ON h_v_charges.version = last_v.last_version
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
ORDER BY h_v_charges.proj_charge_id ASC;

A possibly (because no join is required) faster solution would be:

select *
from (
   select hvc.*, 
          row_number() over (partition by proj_charge_id order by version desc) as rn
   from hist_versions_charges as hvc
   where proj_sous_projet_id = 2  
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

As Colin has pointed out, this can also be written as:

with hv as (
  select hvc.*, 
         row_number() over (partition by proj_charge_id order by version desc) as rn
  from hist_versions_charges as hvc
  where proj_sous_projet_id = 2  
) 
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;

Sql-server – Forcing Join Order

It seems that I can specify OPTION (FORCE ORDER) at the end of the query and that will make the joins happen in the right order. There are lots of people warning not to do this because it stops SQL from optimising my query so I deffinately will be using it sparingly (and watching those queries closely).

Best Answer

Related Solutions

Postgresql – Error “column does not exist” in a SELECT with JOIN and GROUP BY query

Sql-server – Forcing Join Order

Related Question