Skip to content

sum_values

sum_values(arr)

Description

Return the sum of array values

Usage

Call or Deploy sum_values ?
Call sum_values directly

The easiest way to use bigfunctions

  • sum_values function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy sum_values in your project

Why deploy?

  • You may prefer to deploy sum_values in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

sum_values function can be deployed with:

pip install bigfunctions
bigfun get sum_values
bigfun deploy sum_values

Examples

select bigfunctions.eu.sum_values([1, 4, 3])

select bigfunctions.us.sum_values([1, 4, 3])

select bigfunctions.europe_west1.sum_values([1, 4, 3])

+-------+
| value |
+-------+
| 8     |
+-------+

Use cases

You have a table of customer orders, and each order contains an array of item prices. You want to calculate the total value of each order.

Table Schema (Example):

CREATE OR REPLACE TABLE `your_project.your_dataset.orders` AS (
  SELECT 1 AS order_id, [10.50, 25.00, 5.99] AS item_prices UNION ALL
  SELECT 2 AS order_id, [150.00, 12.75] AS item_prices UNION ALL
  SELECT 3 AS order_id, [5.00, 5.00, 5.00, 5.00] AS item_prices
);

Query using sum_values:

SELECT
    order_id,
    bigfunctions.us.sum_values(item_prices) AS total_order_value  -- Replace 'us' with your region
  FROM
    `your_project.your_dataset.orders`;

Result:

+---------+-----------------+
| order_id | total_order_value |
+---------+-----------------+
|       1 |            41.49 |
|       2 |           162.75 |
|       3 |            20.00 |
+---------+-----------------+

This use case demonstrates how sum_values simplifies the process of summing elements within an array, eliminating the need for more complex SQL involving unnest and aggregate functions. It's a very practical application for e-commerce, inventory management, and other scenarios where you need to work with arrays of numeric values.

Need help or Found a bug?
Get help using sum_values

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about sum_values

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on