# Percentage calculation using sub-query returns the wrong result

**URL:** <https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888>\
**Category:** Dashboards\
**Tags:** influxdb, grafana\
**Created:** [April 16, 2020, 4:44pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888 "2020-04-16T16:44:08Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Michael\_Derevyanko](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael_derevyanko/32/4742_2.png) [@Michael\_Derevyanko](https://community.influxdata.com/u/Michael_Derevyanko)\
**Post date:** [April 16, 2020, 4:44pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/1 "2020-04-16T16:44:08Z")

</div>

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

 ![issue](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/5/5ba8616d88e3ea5b7f113bb39d49bfa5acb868d4.png)

Could you please help to understand the issue?

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [April 16, 2020, 7:25pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/2 "2020-04-16T19:25:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Michael\_Derevyanko](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael_derevyanko/32/4742_2.png) [@Michael\_Derevyanko](https://community.influxdata.com/u/Michael_Derevyanko)\
**Post date:** [April 16, 2020, 8:42pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/3 "2020-04-16T20:42:13Z")

</div>

Hi @Anaisdg

It seems the sub quires returns right values

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/6/6a69679f9507299c9da6c404ab373eff6f3b70cf.png)

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [April 17, 2020, 5:42pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/4 "2020-04-17T17:42:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Michael\_Derevyanko](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael_derevyanko/32/4742_2.png) [@Michael\_Derevyanko](https://community.influxdata.com/u/Michael_Derevyanko)\
**Post date:** [April 17, 2020, 6:04pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/5 "2020-04-17T18:04:07Z")

</div>

Hi @Anaisdg

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

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/b/bc16540593f0d385821ff25bd7cb698aaadf5dd3.png)

Could you please give me an example?

---

<div class="post-metadata">

**Author:** ![Michael\_Derevyanko](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael_derevyanko/32/4742_2.png) [@Michael\_Derevyanko](https://community.influxdata.com/u/Michael_Derevyanko)\
**Post date:** [April 17, 2020, 6:53pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/6 "2020-04-17T18:53:34Z")

</div>

@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.

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/c/c43d755a8c6e213d5092825bd7835502aab816fe.png)

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.

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [April 17, 2020, 6:55pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/7 "2020-04-17T18:55:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Michael\_Derevyanko](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael_derevyanko/32/4742_2.png) [@Michael\_Derevyanko](https://community.influxdata.com/u/Michael_Derevyanko)\
**Post date:** [April 22, 2020, 10:52am UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/8 "2020-04-22T10:52:14Z")

</div>

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!

 ![image](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/9/9b4fbfe768d048da746c46171ecf54edbe1996eb.png)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/3X/2/f/2feb065bdfd5e4b7728243aa3b9192ad3e8447c1.png) [@system](https://community.influxdata.com/u/system)\
**Post date:** [April 23, 2020, 3:27pm UTC](https://community.influxdata.com/t/percentage-calculation-using-sub-query-returns-the-wrong-result/13888/9 "2020-04-23T15:27:38Z")

</div>

Closed on April 23, 2020, 3:27pm UTC

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.
