Avoiding duplicates when joining multiple tables

Question

Schema

create table computer (computer_id bigserial primary key, computer_name varchar);

create table computer_hdd (computer_hdd_id bigserial,
              computer_id bigint references computer(computer_id),
              computer_hdd_vendor varchar,
              computer_hdd_capacity int);

create table computer_ram (computer_ram_id bigserial,
              computer_id bigint references computer(computer_id),
              computer_ram_vendor varchar,
              computer_ram_capacity int);

Insert

Create a computer that has 2 HHDs and 2 RAM slots

insert into computer (computer_id, computer_name) values (101, 'desktop-1');

insert into computer_hdd (computer_id, computer_hdd_vendor, computer_hdd_capacity) values (101, 'Seagate', 250);
insert into computer_hdd (computer_id, computer_hdd_vendor, computer_hdd_capacity) values (101, 'Samsung', 250);

insert into computer_ram (computer_id, computer_ram_vendor, computer_ram_capacity) values (101, 'Fujitsu', 4);
insert into computer_ram (computer_id, computer_ram_vendor, computer_ram_capacity) values (101, 'Fujitsu', 4);

Query

Get total HDD capacity and total RAM capacity for every computer

Try JOINs

select C.computer_id,
       sum(CH.computer_hdd_capacity) as hdd_capacity,
       sum(CR.computer_ram_capacity) as ram_capacity
from computer C
         left join computer_hdd CH on C.computer_id = CH.computer_id
         left join computer_ram CR on C.computer_id = CR.computer_id
group by C.computer_id

The result is, as expected, wrong, because the records in the associated tables are basically cross joined so both HDD and RAM are counted twice.

computer_id | hdd_capacity | ram_capacity
------------+--------------+-------------
   101           1000          16

Fix with sub-selects

select C.computer_id,
       (select sum(CH.computer_hdd_capacity) from computer_hdd CH where CH.computer_id = C.computer_id) as hdd_capacity,
       (select sum(CR.computer_ram_capacity) from computer_ram CR where CR.computer_id = C.computer_id) as ram_capacity
from computer C

I assume this is a recurrent scenario in RDBMS querying - avoiding duplication while aggregating buckets.
When interested in the set of values, DISTINCT can be used but it's not the case here.
What is the canonical / recommended / elegant way of solving it ?

Answer 1

Join against the aggregation of each table:

select c.*, 
       hdd.hdd_capacity, 
       ram.ram_capacity
from computer c 
  join (
    select computer_id, sum(computer_hdd_capacity) as hdd_capacity
    from computer_hdd
    group by computer_id 
  ) hdd on hdd.computer_id = c.computer_id
  join (
    select computer_id, sum(computer_ram_capacity) as ram_capacity
    from computer_ram
    group by computer_id
  ) ram on ram.computer_id = c.computer_id

If a computer might not be present in one of the "attribute tables", you probably want a left join instead of the inner join