 # Flux: Sum from Sum and Count

Hello, trying to sum 2 sums in flux and it just not work Individual values are working just fine but I did not find the way how to sum two function (reject and pass) in flux like that

result = reject + pass

See below my custom functions to calculate individual values

countqty = (counttrigtag) =>
from(bucket: “dbase1”)
|> range(\$range)
|> filter(fn: ® =>
r._measurement == “quantity” and r.counttrigtag == counttrigtag and r._field == “countfield”)
|> sum(column: “_value”)

countyel = (lighttrigtag) =>
from(bucket: “dbase1”)
|> range(\$range)
|> filter(fn: ® =>
r._measurement == “interval” and r.lighttrigtag == lighttrigtag and r._field == “lightfield”)
|> count(column: “_value”)

reject = countqty(counttrigtag: “reject”)
pass = countyel(lighttrigtag: “yellow”)

@salvq Right now, you have two streams of tables, `reject` and `pass`. To get a sum need to union the two streams of tables together with `union()`, then group everything into a single table with `group()` and calculate a sum with `sum()`

``````countqty = (counttrigtag) =>
from(bucket: “dbase1”)
|> range(\$range)
|> filter(fn: (r) =>
r._measurement == “quantity” and r.counttrigtag == counttrigtag and r._field == “countfield”)
|> sum(column: “_value”)

countyel = (lighttrigtag) =>
from(bucket: “dbase1”)
|> range(\$range)
|> filter(fn: (r) =>
r._measurement == “interval” and r.lighttrigtag == lighttrigtag and r._field == “lightfield”)
|> count(column: “_value”)

reject = countqty(counttrigtag: “reject”)
pass = countyel(lighttrigtag: “yellow”)

union(tables: [reject, pass])
|> group()
|> sum()
``````

@scott so easy I was on the right path, I did to union but not further Appreciate quick help

See updated function, I was thinking about sum as plus therefore did solve all my desired fucntions How to restructure command below to apply math like multiplication, deduction or divide for example get result of `(total-reject)/total` ?

Function

``````calc = (countlfd) =>
from(bucket: "dbase1")
|> range(\$range)
|> filter(fn: (r) =>
r._measurement == "quantity" and
r._field == "countfld" and
r.trigger == countlfd)
|> drop(columns: ["msgid", "green", "red", "yellow"])
|> sum(column: "_value")

reject = calc(countlfd: "reject")
total = calc(countlfd: "cycle")
union(tables: [reject, total])
``````

Result after union

``````#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,string,string,string,string,long
#group,false,false,true,true,true,true,true,true,false
#default,_result,,,,,,,,
,result,table,_start,_stop,_field,_measurement,deviceid,trigger,_value
,,0,2020-01-17T12:55:12.548614811Z,2020-01-17T13:55:12.548614811Z,countfld,quantity,device1,cycle,60
,,1,2020-01-17T12:55:12.548614811Z,2020-01-17T13:55:12.548614811Z,countfld,quantity,device1,reject,16``````

@salvq This is where `map()` comes in handy. Map rewrites each row in a table based on a function you provide in the `map()` call. In that function, you can perform these types of mathematical operations.

For this to work in your use case, you need to pivot your unioned stream of tables so that `cycle` and `reject` become columns in the same row. After the data is pivoted, you can then use `map()` to do your mathematical operations. Also, I’d convert your integers to floats to keep the calculations as precise as possible:

``````// assume the rest of your query above is here
// ...
union(tables: [reject, total])
|> pivot(rowKey:["_time"], columnKey: ["trigger"], valueColumn: "_value")
|> map(fn: (r) => ({ r with percent_rejected: (float(v: r.reject) / (float(v: r.reject) + float(v: r.cycle))) * 100.00 }))
``````

You can see other examples in the Mathematic operations guide.

@scott thanks for your guidance and example

Finally I ended up with 2 queries which are giving me the same result.

Which one is better (more efficient) or how to evaluate which to pick ?

1st function

``````join(tables:{reject:reject, total:total}, on:["_start"])
|> map(fn:(r) => ({
_value: ((float(v: r._value_total) - float(v: r._value_reject)) / float(v: r._value_total)) * 100.00 }))
``````

2nd function

``````union(tables: [reject, total])
|> pivot(rowKey:["_start"], columnKey: ["trigger"], valueColumn: "_value")
|> map(fn: (r) => ({ rejected: ((float(v: r.cycle) - float(v: r.reject)) / float(v: r.cycle)) * 100.00}))``````

@salvq From an optimization perspective, I’d go with the 1st function. `join` and `map` can both be pretty memory hungry, but with this approach, you’re only performing two transformations on your data. With the 2nd approach, you’re performing three transformations. `union` isn’t particularly heavy, but both `pivot` and `map` can be.

It’d be interesting to see a side-by-side performance comparison between the two approaches, but my gut says the 1st approach would perform a little better.

@scott got it, thanks for help