CODEWITHSUNDEEP
mysqlsqlgoogle-bigquerycalculated-columns
hmims
hmims

Reputation: 539

Sum of 2 fetched columns in other column in Big query SQL

select x,
count(case when i='abc' then 1 else null end) as ele1,
count(case when i='def' then 1 else null end) as ele2,
sum(ele1+ele2) as sum1 from (INNER QUERY)

When i am using sum(ele1+ele2), it is throwing error that ele1 not found. How to fetch sum1 in the same query without using any other outer query?

Upvotes: 2

Views: 3437

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

You cannot use alias as a column name, but if your concern is in verboseness - in your particular case you can write something like below, which is easy readable and skinny enough (for BigQuery Legacy SQL) 

SELECT 
  SUM( i ='abc' ) AS ele1,
  SUM( i = 'def' ) AS ele2,
  SUM( i IN ('abc', 'def') ) AS sum1 
FROM (INNER QUERY)

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You can't use alias as column name in select 

select x,
count(case when i='abc' then 1 else null end) as ele1,
count(case when i='def' then 1 else null end) as ele2,
sum(  ( case when i='abc' then 1 else null end  ) +  
    ( case when i='def' then 1 else null end ) ) as sum1 
from (INNER QUERY)

Upvotes: 2

Related Questions