Aggregations over calculated fields


I need to do an aggregation over a field combination but it doesn’t work.

This works:

SELECT SUM(“first_field”) * SUM(“second_field”) FROM … WHERE …

But what I want is this, and this doesn’t work:

SELECT SUM(“first_field” * “second_field”) FROM … WHERE …

Error Message:

ERR: expected field argument in sum()

Is there any workaround?

Hello @cyclohexan,
Welcome. I believe you want your query to look something like this:
SELECT mean("usage_system")*mean("usage_steal") AS "mult_usage", sum("mult_usage") AS "sum_mult" FROM "telegraf"."autogen"."cpu" WHERE time > :dashboardTime: AND "cpu"='cpu0' GROUP BY time(:interval:) FILL(null)
Let me know if that works!
Thanks :slight_smile:

Thank you for your suggestion but that’s still not what I’m looking for.

For each time point I want to multiply two fields with each other.
Then I want to get the sum of all those calculated products for the current time span.

Unfortunately that’s something else than sum("A") * sum("B") (or mean("A") * mean("B")).

I’m not sure if this is even possible to achieve this with InfluxQL.

Hello @cyclohexan,
Oof you are so right. Sorry, I should have double checked. I believe you want to use a sub query to calculate the mult value at each timestamp/row and then sum them together to get one sum value:

SELECT sum("mult") from ( SELECT "usage_system"*"usage_user" AS "mult" FROM "telegraf"."autogen"."cpu" WHERE time > :dashboardTime: AND "cpu"='cpu0')

Yes, that works.
I wasn’t aware that subselections work in InfluxQL.
Thank you very much.

Good to hear. Anytime :slight_smile: