SQL : Summing the values in a column till first non zero values appears in other column?-CodePudding

Suppose, I have a table t1 looking like

id	value1	value2	wk_id
1	2	0	1
1	1	1	2
1	3	0	3
2	2	1	2
2	2	0	3
3	1	0	2
3	2	0	4
3	3	0	5

And I want to sum up the value1 till non-zero value appears on the value2 for first time. End product must look like this:

id	value1
1	2
2	0
3	6

How to perform this in SQL?

CodePudding user response：

If your MySQL version support window function you can try to use SUM window function with condition aggregate function be a flag to represent your logic (till non-zero value appears on the value2 for first time)

Then do condition aggregate function again.

Query #1

SELECT id,
       SUM(CASE WHEN flag = 0 THEN value1 ELSE 0 END) value1
FROM (
  SELECT *,
         SUM(CASE WHEN value2 = 1 THEN -1 ELSE 0 END) OVER(PARTITION BY ID ORDER BY wk_id) flag
  FROM T 
) t1
GROUP BY id;

id	value1
1	2
2	0
3	6

View on DB Fiddle

CodePudding user response：

WITH cte AS (
    SELECT *,
           CASE WHEN SUM(value2) OVER (partition by id ORDER BY wk_id) = 0
                THEN SUM(value1) OVER (partition by id ORDER BY wk_id) 
                ELSE 0
                END sum_value1
    FROM test
    ORDER BY id, wk_id
)
SELECT id, MAX(sum_value1) sum_value1
FROM cte
GROUP BY id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0fcdca008e4a821f952de4d608434bcf

CodePudding user response：

One option is a NOT EXISTS clause, looking for the stop row (the first row with value2 = 1).

select id, sum(value1)
from mytable
where not exists
(
  select null
  from mytable stoprow
  where stoprow.id = mytable.id
  and stoprow.wk_id <= mytable.wk_id
  and stoprow.value2 = 1
)
group by id
order by id;

CodePudding user response：

Aggregating on a calculated rolling total of value2 can also be done via a self-join.

select id
, sum(if(roll_tot_value2=0,value1,0)) as total
from 
(
  select t1a.id, t1a.wk_id, t1a.value1
  , sum(t1b.value2) as roll_tot_value2
  from t1 as t1a
  join t1 as t1b
    on t1b.id = t1a.id
   and t1b.wk_id <= t1a.wk_id
  group by t1a.id, t1a.wk_id, t1a.value1
) q
group by id;

id	total
1	2
2	0
3	6

Test on db<>fiddle here

CodePudding user response：

You can use a subquery:

select t.id, coalesce(
   (select sum(t2.value1) from t1 t2 where t2.value2 = 0 and t2.id = t.id 
       and (not exists (select 1 from t1 t3 where t3.value2 = 1 and t3.id = t.id) 
       or t2.wk_id < (select min(t4.wk_id) from t1 t4 where t4.id = t.id and t4.value2 = 1))), 0) 
from t1 t group by t.id