CODEWITHSUNDEEP
sqlgoogle-bigquery
Harshith S H
Harshith S H

Reputation: 13

How to do aggregation with calculation in SQL

enter image description here

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

enter image description here

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.

Upvotes: 0

Views: 79

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

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

enter image description here

Upvotes: 2

Mohammad
Mohammad

Reputation: 678

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;

Upvotes: 1

Related Questions