# Trouble to write a query making two counts on a relative period of time and dividing the counts

**URL:** <https://discuss.elastic.co/t/trouble-to-write-a-query-making-two-counts-on-a-relative-period-of-time-and-dividing-the-counts/258827>\
**Category:** Elasticsearch\
**Created:** [December 16, 2020, 9:14am UTC](https://discuss.elastic.co/t/trouble-to-write-a-query-making-two-counts-on-a-relative-period-of-time-and-dividing-the-counts/258827 "2020-12-16T09:14:03Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Cedric\_Remond](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cedric_remond/32/80955_2.png) [@Cedric\_Remond](https://discuss.elastic.co/u/Cedric_Remond)\
**Post date:** [December 16, 2020, 9:14am UTC](https://discuss.elastic.co/t/trouble-to-write-a-query-making-two-counts-on-a-relative-period-of-time-and-dividing-the-counts/258827/1 "2020-12-16T09:14:03Z")

</div>

I have been trying to write a query for a few days now, and I began to worry it won't be possible.

My goal is two make two counts (full-text queries) over a relative period of time (ie the last 24 hours), and my final result must be the division of the first and the second count.

My use case is similar to the example in this page of the doc : [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html).

I have two sets of keywords: `set_a` and `set_b`.  
I need to perform a first query with `set_a U set_b`, and then on the results a second query with `set_b`. Finally, I need to divide the two resulting counts.

Here is the query I have right now:

```auto
GET _search
{
  "aggs": {
    "risk": {
      "filters": {
        "filters": {
          "company_and_risk": {
            "match": {
              "text": {
                "query": "apple AND pollution"
              }
            }
          },
          "risk": {
           "match": {
            "text": {
              "query": "pollution"
              }
            }
          }
        }
      },
      "aggs": {
        "risk-percentage": {
          "bucket_script": {
            "buckets_path": {
              "companyAndRisk": "company_and_risk",
              "risk": "risk"
            },
            "script": "params.companyAndRisk / params.risk * 100"
          }
        }
      }
    }
  }
}

```

I assume I need some intermediary count with `value_count` for each bucket, but I don't know how to do this with a `filters` aggregation. Also my `buckets_path` are wrong, and the filter to get only the last 24 hours data points is missing.

I absolutely need to do all of this at once, I cannot perform multiple queries.

Could anyone help finish this query please ? (or tell me if this is impossible)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 13, 2021, 9:14am UTC](https://discuss.elastic.co/t/trouble-to-write-a-query-making-two-counts-on-a-relative-period-of-time-and-dividing-the-counts/258827/2 "2021-01-13T09:14:07Z")

</div>

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