Check and return a flag on SQL which would return 1 if all entries are 0?-CodePudding

flag 1	fruit	required output
0	apple	0
0	apple	0
0	apple	0
1	apple	0
0	orange	1
0	orange	1
0	orange	1

I tried using window functions such as row_number and any function. what would be the best way to approach this type of question in SQL?

CodePudding user response：

Using windowed SUM:

SELECT flag1, fruit, 
  CASE WHEN SUM(flag1) OVER(PARTITION BY fruit) = 0 THEN 1 ELSE 0 END
FROM tab;

db<>fiddle demo

CodePudding user response：

You can try the following

--this calculates the required flag for each fruit
with flags (fruit, flagsum) as (
  select fruit, 1 - sign(sum(flag)) as flagsum
  from fruits
  group by fruit
)
--join with the original table
select fr.*, fl.flagsum
from fruits fr inner join flags fl on fr.fruit = fl.fruit

sign(sum(flag)) returns 1 if there is at least one flag with value 1 in that group and 0 otherwise. So 1 - sign(sum(flag)) inverts that value.