Conditional filter in SELECT to avoid JOIN

Question

I'm wondering if there's an optimized, more readable way of doing the following in BigQuery:

select
  coalesce(a.item, b.item) as item,
  a.cost,
  b.flag_true_cost,
from
(
  select
  item,
  sum(cost) as cost,
  from test.t1
  group by item
) a
full join
(
  select
  item,
  sum(cost) as flag_true_cost
  from test.t1
  where flag = true
  group by item
) b
on a.item = b.item;

Output:

item    cost    flag_true_cost
pumpkin 3.5     3.5
apple   1.4     1.4
kale    20.3    2.3

Here is some pseudo-code of what I had in mind

select
item,
sum(cost) as cost,
if(flag=true, sum(cost)) as flag_true_cost
from test.t1
group by item;

Here is the sample data

create table test.t1 (item string, flag bool, cost numeric);

insert into test.t1 values
  ('kale', true, 2.3),
  ('kale', false, 18),
  ('apple', true, 1.4),
  ('pumpkin', true, 3.5)
;

Answer 1

Here is some pseudo-code of what I had in mind

You are almost there. Just move the conditional within the aggregate function:

select item, sum(cost) as cost,
    sum(if(flag=true, cost, 0)) as flag_true_cost
from ayima-ad-hoc.test.t1
group by item;