How to add a column with constant value in each row using BigQuery

user_13415783 0
Viewed 1945

Hello I am new to big data and BigQuery. I have a following table:

colA colB
 1     2
 3     4
 2     5

Now I want to create a new column that will have values that equals to sum(colA)/sum(colB) So it should look something like this:

colA  colB  colC
 1     2    0.54
 3     4    0.54
 2     5    0.54

So to achieve this I write the following query:

Sum(colA)/sum(colB) as colC

But this is what I get:

colA  colB  colC
 1     2    0.5
 3     4    0.75
 2     5    0.4

Could you help me on this.. I am trying to understand what I am doing wrong.

google-bigquery sql
3 Answers

try like below

   select cola,colb,
   (select sum(cola)/sum(colb) from table_name) as colc
   from table_name
user_68085830

Your query is taking sum(A) for each row, which is why you got incorrect results. You need to sum the entire column.

For a beginner, I would probably use

with data as (select 1 as colA, 2 as colB union all select 3,4 union all select 2,5)
select colA, colB, (select sum(colA)/sum(colB)) as colC 
from data

For the more advanced, you can use empty window functions to sum the entire column

with data as (select 1 as colA, 2 as colB union all select 3,4 union all select 2,5)
select colA,colB, (sum(colA) over() / sum(colB) over()) as colC
from data
user_116438580

for beginner - I would suggest below (BigQuery Standard SQL)

#standardSQL
select colA, colB, colC
from `project.dataset.table`,
(select sum(colA) / sum(colB) as colC from `project.dataset.table`)
user_52219440
Related