Dividing results of two sub-queries fails [InfluxDB 1.8.2]

InfluxDB 2 Dashboards
,
1

So I need to divide two scalar values produced by two queries to a single measurement, and I came up with the following solution (I use Grafana for visualisation):

SELECT sum("errors") / sum("total") AS "errorPercentage" FROM (
  SELECT count("duration") AS "total" FROM "requests" WHERE [[timeFilter]]
), (
  SELECT count("duration") AS "errors" FROM "requests" WHERE [[timeFilter]] AND "result" = 'KO'
)

As you may have guessed, a goal is simple, calculate error percentage for some of my custom requests data.

Each sub-query produces a valid result, but when combined like that they produce nothing.

A bit of debugging information:

If I query like this:

SELECT sum("errors") as "errors", sum("total") as "total" FROM (
  SELECT count("duration") AS "total" FROM "requests" WHERE [[timeFilter]]
), (
  SELECT count("duration") AS "errors" FROM "requests" WHERE [[timeFilter]] AND "result" = 'KO'
)

A result looks like this:

image
image1634Ă—166 10.5 KB

Switching sub-queries produces a different result:

SELECT sum("errors") as "errors", sum("total") as "total" FROM (
  SELECT count("duration") AS "errors" FROM "requests" WHERE [[timeFilter]] AND "result" = 'KO'
), (
  SELECT count("duration") AS "total" FROM "requests" WHERE [[timeFilter]]
)

I can’t include more than one image sadly, but result is “errors” field has a valid value, when “total” is empty

Looks like issue is with the second sub-query.

But if I don’t use any aggregate functions on main query like this (sub-queries position is same as for initial variant):

SELECT "errors", "total" FROM (
  SELECT count("duration") AS "total" FROM "requests" WHERE [[timeFilter]]
), (
  SELECT count("duration") AS "errors" FROM "requests" WHERE [[timeFilter]] AND "result" = 'KO'
)

The result is same as for previous query, where result is “errors” field has a valid value, when “total” is empty

Now second sub-query is calculated, but not the first one.

I’m a bit confused, may be someone will help me with this issue?

1 Like
2

I think this might be something you could do with Kapacitor

hope that helps

3

Sadly, I need a pure influxql solution for this task. I’ve figured out a solution using Flux, but for the same reason I can’t use it in my situation.

I’ve seen it done before in the internet and tried to mimic the solution, but with no results.

Link to mentioned solution:

4

I believe you will want to use a “Transform”, create 2 Quieries then add 2 Transforms
Query 1:

SELECT count("duration") AS "total" FROM "requests" WHERE [[timeFilter]]

Query 2:

SELECT count("duration") AS "errors" FROM "requests" WHERE [[timeFilter]] AND "result" = 'KO'

Transform 1:

Outer Join: Time

Transform 2:

Binary Operation: errors / total

But, in my case this isn’t sufficient as I need to programmatically do a Binary Operation across tags. However, I just found that there is a “Group By” transform option in Grafana 7.2.

5

remove the count from inner subqueries and put it in outer one,