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

Postgresql – Sum a column based on a condition in another column with partitions

I’m trying to sum a column based on a condition in another column with partition by in SQL, but it’s not working. So I hope somebody can help me with this.

My table is like this:

Group_1 Group_2 Date Value
A D 01/01/2021 1
A D 01/02/2021 3
A E 01/03/2021 5
B D 01/01/2021 7
B D 01/02/2021 9
B E 01/03/2021 11
B D 01/05/2021 17
B D 01/03/2021 13
B E 01/04/2021 13
C D 01/01/2021 7
C D 01/02/2021 10

So, I need to sum the values of [Value] for all rows where there is a ‘D’ on [Group_2] that is older than the first ‘E’ on the same group (if it exists) for each group of [Group_1].

And the result should be like:

Group_1 Group_2 Sum
A D 4
B D 16
C D 17

Anybody knows how can I solve this kind of problem?

Tags:

2

Answers


  1. 0

    Try the following aggregation with NOT EXISTS:

    SELECT Group_1, Group_2, SUM(Value) AS Value_Sum
FROM table_name T
WHERE Group_2 <> 'E' AND
NOT EXISTS (SELECT 1 FROM table_name D 
            WHERE D.Group_1 = T.Group_1 AND 
                  D.Group_2 = 'E' AND 
                  D.Date <= T.Date)
GROUP BY Group_1, Group_2
ORDER BY Group_1, Group_2

    See a demo.

    Login or Signup to reply.
  2. 0 
    select   group_1
        ,group_2
        ,sum(value)
from
(
select   group_1
        ,group_2
        ,case when count(case when group_2 = 'E' then 1 end) over(partition by group_1 order by date) = 0 then value end as value
from     t
) t
group by group_1, group_2
having   group_2 = 'D'
    group_1 group_2 sum
    A D 4
    B D 16
    C D 17

    Fiddle

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

Back To Top
Search