CODEWITHSUNDEEP
sqlpostgresqljoinsumaggregate
Luca Riviera
Luca Riviera

Reputation: 13

SUM over distinct rows with multiple joins

I need to create a query that brings the sum of the totals of orders moved between January 1, 2006 and January 31, 2006, by resource name, person's name and person's uf code, regardless of whether person, resource, uf fields are filled.

I have this consultation so far.

Create table Ordered(
    oid int,        
    movement date,     
    pid int,        
    rid int,          
    amount double,     
    unitary double,
    total double, 
    Primary key (OID),
    Constraint fk_PID Foreign key (PID) references People (PID),
    Constraint fk_RID Foreign key (RID) references Resource (RID)
    );

Create table Resource(
    rid int,          
    code Varchar(25),     
    name Varchar(150),        
    Primary key (RID)
    );
    
Create table People(
    pid int,          
    code varchar(25),     
    name varchar(150),        
    FU int,        
    Primary key (PID),
    Constraint fk_FID Foreign key (FID) references FU (FID)
    );
    
Create table FU(
    fid int,          
    code varchar(25),     
    name varchar(150),
    Primary key (Fid)
    );

select r.name,p.name,f.code,sum(o.total) from Ordered o 
    right join resource r on o.rid = r.rid right join 
    People p on o.pid = p.pid 
    right join fu f on p.fid = f.fid where o.movement >= '2006-01-01' and o.movement <= '2006-01-31' group by r.name,p.name,f.code

My problem is that the query is not listing when resources or people are null

With the following query (http://www.sqlfiddle.com/#!9/c0f4fe/4)

Upvotes: 0

Views: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I'm not 100% sure what you want. But if you want to list all people, even those with no orders, then start with people and use left join:

select r.name, p.name, f.code,sum(o.total)
from People p left join
     Ordered o
     on o.pid = p.pid and
        o.movement >= '2006-01-01' and o.movement <= '2006-01-31' left join
     resource r
     on o.rid = r.rid left join
     fu f
     on p.fid = f.fid 
group by r.name,p.name,f.code

Upvotes: 1

Related Questions