CODEWITHSUNDEEP
google-bigqueryoperators
Levi H.
Levi H.

Reputation: 105

How can I sum multiple NUMERIC fields in BigQuery?

I have the following table, where all the numbers are NUMERIC:

Campaign_cost_month LinkedIn_Ads_1 LinkedIn_Ads_2
2021.01 85.46 12.37

I want to sum multiple fields (here I only show two of them) into a total field, like this:

Campaign_cost_month LinkedIn_Ads_1 LinkedIn_Ads_2 Total_cost
2021.01 85.46 12.37 97.83

I tried to use ordinary operators like this, but it gives empty results for the Total_cost:

SELECT 
Campaign_cost_month,
LinkedIn_Ads_1,
LinkedIn_Ads_2,
LinkedIn_Ads_1 + LinkedIn_Ads_2 AS Total_cost
FROM dataset

Upvotes: 1

Views: 4871

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

I want to sum multiple fields (here I only show two of them) into a total field

Consider below approach

create temp function custom_sum(arr array<float64>) as ((
  select sum(value) from unnest(arr) value
));
select *, custom_sum([LinkedIn_Ads_1, LinkedIn_Ads_2, LinkedIn_Ads_3]) Total_cost
from `project.dataset.table`

if apply to sample data in your question - output is

enter image description here

As you can see here - you don't need to take care of handling NULLs for each column separatelly - rather you just provide your columns in the array and UDF takes care of you :o)

As an additional benefit of this approach - it is easy can be refactor into dynamically building that array - so you don't need to do it manually - check my other posts - I have plenty of example of this approach

Upvotes: 1

Chandra Mochahary
Chandra Mochahary

Reputation: 405

SELECT 
Campaign_cost_month,
LinkedIn_Ads_1,
LinkedIn_Ads_2,
COALESCE(LinkedIn_Ads_1,0) + COALESCE(LinkedIn_Ads_2,0) AS Total_cost
FROM dataset

COALESCE returns the value of the first non-null expression.
eg: COALESCE(a,b), here it will use value a but if it is NULL then it will use value b.

Upvotes: 3

Related Questions