1

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 ?

1 Answer 1

3

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

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.