Bigquery - Sum Product of multiple value within two columns

user_14165286 0
Viewed 245

I have this value

data sample I want to calculate a new column, which will add the product of multiplication from ticket_units_count and price, so it must be: 5 * 33104.0 + 4 * 23449.0 = 259316

How to do that in bigquery? I tried this one

SELECT 
SUM(CAST(price AS FLOAT64) * CAST(ticket_units_count AS INT64))
FROM table

But it shows this error: Bad double value: 33104.0;23449.0 Need your help to specify the query to get the expected result

sumproduct google-bigquery multiplication
1 Answers

Consider below approach

select *, 
  ( select sum(cast(_count as int64) * cast(_price as float64))
    from unnest(split(ticket_units_count, ';')) _count with offset
    join unnest(split(price, ';')) _price with offset
    using (offset)
  ) as total
from your_table

if applied to sample data in your question - output is

enter image description here

user_52219440
Related