Question 89721 in PostgreSQL Question posted in
The official documentation can be found here.

Avoiding double counting values when aggregating tables used in joins – Postgresql

I have two tables that have a parent/child relationship, where one record in the parent table may correspond to N records in the child. Both tables have an amount column that I want to aggregate in one query, so I can see the total amount for the parent and the children.

When I join the tables together, the parent amounts will be counted multiple times for each child resulting in the parent aggregate values to be incorrect.

Here is a simplified version of the problem that has the bad results and my desired results.

drop table if exists parent;
CREATE TABLE parent (
    id numeric,
    amount numeric,
    person text
);

drop table if exists child;
CREATE TABLE child (
    id numeric,
    parentId numeric,
    amount numeric,
    person text
);

insert into parent (id, amount, person) values 
(1, 5, 'P1'),
(2, 15, 'P1'), 
(3, 5, 'P2'), 
(4, 20, 'P2');

insert into child (id, parentId, amount) values 
(1, 1, 3),
(2, 1, 5), 
(3, 2, 10), 
(4, 3, 6),
(5, 4, 12), 
(5, 4, 8);

-- Parent is double counted for each child joined onto
select
    p.person,
    p.id,
    sum(p.amount) as parent_sum,
    sum(c.amount) as child_sum
from
    parent p
left outer join child as c on
    c.parentId = p.id
group by rollup (p.person, p.id)
order by (p.person, p.id)

/*
Output:
| person |     id | parent_sum | child_sum |
|--------|--------|------------|-----------|
|     P1 |      1 |         10 |         8 |
|     P1 |      2 |         15 |        10 |
|     P1 | (null) |         25 |        18 |
|     P2 |      3 |          5 |         6 |
|     P2 |      4 |         40 |        20 |
|     P2 | (null) |         45 |        26 |
| (null) | (null) |         70 |        44 |


Desired output:
| person |     id | parent_sum | child_sum |
|--------|--------|------------|-----------|
|     P1 |      1 |          5 |         8 |
|     P1 |      2 |         15 |        10 |
|     P1 | (null) |         20 |        18 |
|     P2 |      3 |          5 |         6 |
|     P2 |      4 |         20 |        20 |
|     P2 | (null) |         25 |        26 |
| (null) | (null) |         45 |        44 |
*/

Here is an sql fiddle showing this: http://sqlfiddle.com/#!17/c4af6/2

I think I might be able to get this to work using a window function but I am looking for a better solution that is as performant as possible. Any ideas on this?

Tags:

2

Answers


  1. 0

    You can sum first up and then join, the results

    SQL Fiddle

    Query 1:

    -- Parent is double counted for each child joined onto
select
    p.person,
    p.id,
     SUM(parent_sum) as parent_sum,
    SUM(child_sum) as child_sum
from
    (SELECT     p.person,
    p.id,
    sum(p.amount) as parent_sum FROM parent p 
    GROUP BY p.person,
    p.id) p
left outer join (SELECT     
    c.parentId,
    sum(c.amount) as child_sum FROM child c               
                GROUP BY c.parentId) c on
    c.parentId = p.id
group by rollup (p.person, p.id)
order by (p.person, p.id)

    Results:

    | person |     id | parent_sum | child_sum |
|--------|--------|------------|-----------|
|     P1 |      1 |          5 |         8 |
|     P1 |      2 |         15 |        10 |
|     P1 | (null) |         20 |        18 |
|     P2 |      3 |          5 |         6 |
|     P2 |      4 |         20 |        20 |
|     P2 | (null) |         25 |        26 |
| (null) | (null) |         45 |        44 |
    Login or Signup to reply.
  2. 0

    Using rollup will get you the extra rows but I think you have to do the summary a bit manually like this:

    select p.person, p.id,
    coalesce(
        case when grouping(p.id)     = 0 then min(p.amount) end,
        case when grouping(p.id)     = 1 then sum(case when grouping(p.id) = 0 then min(p.amount) end) over (partition by p.person) end,
        case when grouping(p.person) = 1 then sum(case when grouping(p.id) = 0 then min(p.amount) end) over () end,
        0
    ) as parent_sum,
    sum(c.amount) as child_sum
from parent p left outer join child as c on c.parentId = p.id
group by rollup(p.person, p.id)
order by p.person, p.id;

    You should be able to extend the pattern to deeper levels if necessary.

    https://dbfiddle.uk/CxD1n2T1

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search