PostgreSQL sum some values together and don't for other-CodePudding

SELECT
    t.id,
    sum(o.amount),
    t.parent_id
FROM tab t
         LEFT JOIN order o ON o.deal = t.id
GROUP BY t.id

Current output:

id	sum	parent_id
1	10
2	10
3	15	5
4	30	5
5	0
6	0	8
7	0	8
8	20

Desired logic, if the row contains parent_id then skip it but add everything together in the sum field so for id 3,4,5 the total would be 45 and only the id 5 would be shown. There can be cases when the sums are in the "sub tabs" or in the "main tab" but everything should be summed together. Desired output:

id	sum	parent_id
1	10
2	10
5	45
8	20

What have I tried so far is to do sub-selects and played around with group by. Can someone point me to the right direction?

CodePudding user response：

Use coalesce().

with the_data(id, sum, parent_id) as (
values
    (1, 10, null),
    (2, 10, null),
    (3, 15, 5),
    (4, 30, 5),
    (5, 0, null),
    (6, 0, 8),
    (7, 0, 8),
    (8, 20, null)
)

select coalesce(parent_id, id) as id, sum(sum)
from the_data
group by 1
order by 1

Read about the feature in the documentation.

Db<>fiddle.

CodePudding user response：

Your query isn't valid in PostgreSQL:

SELECT
    t.id,
    sum(o.amount),
    t.parent_id
FROM tab t
         LEFT JOIN order o ON o.deal = t.id
GROUP BY t.id

Unlike MySQL, PostgreSQL doesn't have implicit GROUP BY columns (unless something changed recently).

Anyway, if you're using t.id in your GROUP BY clause, then each t.id will produce one row, so you'll always have 3 and 4 separated, for example.

It looks like you're trying to use the parent_id as the main criterion to group by, falling back on the id when the parent_id is NULL.

You could use COALESCE(t.parent_id, t.id) to get this value for each row, and then group using it.

For example:

SELECT
    COALESCE(t.parent_id, t.id),
    SUM(o.amount)
FROM tab t
         LEFT JOIN order o ON o.deal = t.id
GROUP BY COALESCE(t.parent_id, t.id)