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:

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:

Anybody knows how can I solve this kind of problem?

CodePudding user response：

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.

CodePudding user response：

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'

Fiddle