CASE statement with window function

Question

Table

Could someone assist I am looking for a solution to get the counts correct for each store item pair. The first count is easy

COUNT (*) OVER(PARTITION BY store ORDER BY s.deptitemcode DESC) StoreItemSeqNo

However for the 2nd count, I only want the count if the Flag is true for each store item pair if the flag is false then the count should be the previous value if there was no previous value it should be zero.

Refer to table example

Answer 1

Aggregate functions can instead of * also take an expression. If COUNT(..) is called for an expression and not *, it counts everything except NULL. So, to count according to flag:

COUNT (CASE WHEN flag = 1 THEN flag ELSE NULL END) OVER(PARTITION BY store ORDER BY s.deptitemcode DESC) StoreItemSeqNo