Using query fields for other fields in BigQuery

[i2afaur]

Hello, I have a question that I can't solve,
How do you do in bigquery to store the value of a subquery and use it in another field?

For example let's say that I have a subquery that returns me the number of passengers of a route. Then I want to do a compariso with that value for another field. The following code is correct for MySQL, how can I do the same procedure for BigQuery?

(SELECT @aux := COUNT(*) FROM consumptions WHERE route = "X1"),
IF(@aux = 0 ,@aux:= another_field, @auxpax) AS total_consumptions

I have tried several things such as using DECLARE/SET, ...

Help would be really appreciated 🙂

[anjelab]

Can you give sample data, schema and your desired output for this scenario?

[RC1]

@i2afaur 
Not the optimal one but should do the job for you.

Can you try once 

select 
case when (SELECT  COUNT(*) FROM consumptions WHERE route = "X1")=0 then
field1
else
field2
end
from table1