CODEWITHSUNDEEP
postgresqlsum
sibert
sibert

Reputation: 2228

Correct sum with multiple subrecords (postgresql)

This have maybe been asked several times before, but I do not how to achieve a correct som from both parent and child.

Here is the tables:

CREATE TABLE co
    (coid int4, coname text);    
INSERT INTO co
    (coid, coname)
VALUES
    (1, 'Volvo'),
    (2, 'Ford'),
    (3, 'Jeep'),
    (4, 'Toyota')

;

CREATE TABLE inv
    (invid int4, invco int4, invsum numeric(10,2));
INSERT INTO inv
    (invid, invco, invsum)
VALUES
    (1,1,100),
    (2,1,100),
    (3,2,100),
    (4,3,100),
    (5,4,100)
;

CREATE TABLE po
    (poid int4, poinv int4, posum int4);
INSERT INTO po
    (poid, poinv, posum)
VALUES
    (1,1,50),
    (2,1,50),
    (3,3,100),
    (4,4,100)
;

I started with this simple query 

SELECT    coname, sum(invsum)
FROM      inv
LEFT JOIN co ON coid=invco
GROUP BY  1
ORDER BY  1

Which gave a correct result:

coname     sum
Ford       100
Jeep       100
Toyota     100 
Volvo      200

Then I added the po record and the sums became incorrect:

SELECT    coname, sum(posum) as po, sum(invsum)
FROM      inv
LEFT JOIN co ON coid=invco
LEFT JOIN po ON poinv=invid
GROUP BY  1
ORDER BY  1

Which multiplied the sum for Volvo:

coname    po     sum
Ford      100    100
Jeep      100    100
Toyota    (null) 100 (no records for po = correct)
Volvo     100    300 (wrong sum for inv)

How do I construct a query that gives correct result with multiple subrecords of po? (Window function?)

Sqlfiddle: http://sqlfiddle.com/#!15/0d90c/12

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Do the aggregation before the joins. This is a little complicated in your case, because the relationship between co and po seems to require inv:

SELECT co.coname, p.posum, i.invsum
FROM co LEFT JOIN
     (SELECT i.invco, sum(i.invsum) as invsum
      FROM inv i
      GROUP BY i.invco
     ) i
     ON co.coid = i.invco LEFT JOIN
     (SELECT i.invco, sum(po.posum) as posum
      FROM po JOIN
           inv i
           ON po.poinv = i.invid
      GROUP BY i.invco
     ) p
     ON co.coid = p.invco
ORDER BY 1;

Note: I presume the logic is to keep everything in the co table, even if there are no matches in the other tables. The LEFT JOIN should start with this table, the one with all the rows you want to keep.

Upvotes: 1

Related Questions