Percentage calculation using sub-query returns the wrong result

[Michael_Derevyanko]

Hi all faced with division issue while percentage calculation using subqueries.

My goal is to execute the formula percentage = (f1000_value * 100)/total_value; The f1000_value represents the count of the requests where response time is less or equal 1000 ms.

So I built the query like this:

SELECT  (mean("f1000")*100) /mean("total") as percentage 
FROM
(SELECT count("responseTime") as "f1000" FROM "requestsRaw" WHERE  "requestName" =~ /->/ AND "requestName" !~ /^SCRIPT/ AND $timeFilter AND responseTime <=1000 ),
(SELECT count("responseTime") as "total" FROM "requestsRaw" WHERE  "requestName" =~ /->/ AND "requestName" !~ /^SCRIPT/ AND $timeFilter)
GROUP BY time([[aggregation]]s), percentage 

But I’m getting wrong value, percentage = (1840*100)/2792; percentage = 65.9; Each subquery returns right result. But the calculated value was unexpected.

I tried to set the exact value of total and I got the right percentage value. Please see screen bellow

Could you please help to understand the issue?

[Anaisdg]

Hello @Michael_Derevyanko,
Can you please provide the output of your two sub queries?
This might be a silly question, but did you mean to include mean("f1000") in your query? It seems like your subqueries/count() should just return one value each.

[Michael_Derevyanko]

Hi @Anaisdg

It seems the sub quires returns right values

[Anaisdg]

Hello. @Michael_Derevyanko,
what happens when you remove the mean() from your percentage query?

[Michael_Derevyanko]

Hi @Anaisdg

It seems that my query is not correct, due to “No data points” message

Could you please give me an example?

[Michael_Derevyanko]

@Anaisdg, I would like to add some things …

No issues for the query like that:
SELECT sum(“countError”)/sum(“count”)*100 AS “rate” FROM “$measurement” WHERE “transaction”=‘all’ AND $timeFilter AND “application” =~ /$scenario/

But the issue with subquery is actual for me, my project has blocked… I spent several days to solve the issue, have no luck… I tried another db… the same issue… so the point is to get two different numbers from two subqueries then play with that

After investigation, I’ve found out that subtraction works perfectly.

Looks like an issue for the division operations from my point of view while using subqueries like my. Please corrector me if I’m wrong.

[Anaisdg]

That is odd. I don’t know. This query works for me.
SELECT "count_n_containers_running"/"count_n_containers_stopped"*100 FROM (SELECT count("n_containers_running") AS "count_n_containers_running", count("n_containers_stopped") AS "count_n_containers_stopped" FROM "docker"."autogen"."docker" WHERE time > :dashboardTime: GROUP BY time(:interval:) FILL(null))
I haven’t had problems with the division. Have you tried the query on influxdb directly? In the meantime I’m asking around to see I can find some answers. It seems like it’s a grafana parsing issue.

[Michael_Derevyanko]

Hi @Anaisdg

I have found the workaround, I added variable - the query to calculate the total request and then applied the variable in the target query (where the percentage is calculating) instead of sub-query using. I hope my investigation can help to someone. So the issue has been solved for my project.
Anyway thank you for the support!