How to do aggregation with calculation in SQL-CodePudding

I have similar table with two columns, C2 will have multiple values . I need the output as

Condition for t2.c2 is If all the values in t1.C2 are <= 5, then 1 else 0, please advice what would be the best logic.

CodePudding user response：

This will give desired result. First section has t1 table data, second section has t2 data based on logic requested.


with t1 as 
(select 'A10' as C1, 2 as C2 union all
select 'A10' as C1, 3 as C2 union all
select 'A10' as C1, 4 as C2 union all
select 'A10' as C1, 5 as C2 union all
select 'A10' as C1, 3 as C2 union all
select 'A10' as C1, 4 as C2 union all
select 'A10' as C1, 2 as C2 union all
select 'A10' as C1, 4 as C2 union all
select 'A10' as C1, 5 as C2 union all
select 'A10' as C1, 3 as C2 )

select C1 , if(MAX(C2) <=5,1,0) as C2
from t1 group by C1;

CodePudding user response：

Another option

select distinct C1, 
  if(logical_and(C2 <= 5) over(partition by C1), 1, 0) as C2
from your_table

if applied to sample data in your question - output is