Correct sum with multiple subrecords (postgresql)

Question

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

Kamil Gosciminski · Accepted Answer · 2016-09-18 14:27:44Z

1

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.

edited Sep 18, 2016 at 14:27

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

answered Sep 18, 2016 at 14:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kamil Gosciminski Over a year ago

Isn't it the other way around? co seems to be just a dictionary table for inv.invco, so shouldn't you start with inv?

sibert Over a year ago

I want to make a list of invoices with sums. I suppose this will give all companies even those without invoices?

Kamil Gosciminski Over a year ago

@sibert change first LEFT JOIN to RIGHT JOIN. Gordon, I've corrected an alias in your query.

sibert Over a year ago

Does not help. There will be thousands of companies that will have no invoices. They will have (null) values: sqlfiddle.com/#!15/ab645/1

Gordon Linoff Over a year ago

@sibert . . . If you only want companies that have invoices, then use an inner join instead of a left join.

|

Collectives™ on Stack Overflow

Correct sum with multiple subrecords (postgresql)

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related