# Cannot find the way to calculate average count of events per hour in Elasticsearch

**URL:** <https://discuss.elastic.co/t/cannot-find-the-way-to-calculate-average-count-of-events-per-hour-in-elasticsearch/94356>\
**Category:** Elasticsearch\
**Created:** [July 24, 2017, 3:47pm UTC](https://discuss.elastic.co/t/cannot-find-the-way-to-calculate-average-count-of-events-per-hour-in-elasticsearch/94356 "2017-07-24T15:47:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ICG](https://avatars.discourse-cdn.com/v4/letter/i/4491bb/32.png) [@ICG](https://discuss.elastic.co/u/ICG)\
**Post date:** [July 24, 2017, 3:47pm UTC](https://discuss.elastic.co/t/cannot-find-the-way-to-calculate-average-count-of-events-per-hour-in-elasticsearch/94356/1 "2017-07-24T15:47:43Z")

</div>

I have several events occurred on different dates and hours. I want to know the average count of events per hour. How can I do it in Elasticsearch?

This is my mapping:

```
PUT /test
{
    "mappings": {
      "registry": {
      "_source": {
        "enabled": true
      },
      "_all": {
        "enabled": true
      },
        "properties": {
          "Id": {
            "type":"text"
          },
          "Location": {
            "type":"geo_point"
          },
          "Country": {
            "type":"keyword"
          },
          "EventDate": {
            "type":"date",
            "format": "yyyy-MM-dd HH:mm:ss"
          },
        }
      }
    }
}

```

For example I have the following dates:

```
2017-03-22 15:14:22
2017-03-22 15:15:21
2017-03-22 16:15:00
2017-03-23 15:10:20
2017-03-23 16:09:20

```

Let's put this data into Elasticsearch:

```
POST test/registry/_bulk
{"index":{"_id":"1"}}
{"Id":"111","Location":{ "lat": 41.12,"lon": -71.34},"Country":"ESP","EventDate":"2017-03-22 15:14:22.0"}
{"index":{"_id":"2"}}
{"Id":"222","Location":{ "lat": 41.22,"lon": -73.34},"Country":"ESP","EventDate":"2017-03-22 15:15:21.0"}
{"index":{"_id":"3"}}
{"Id":"333","Location":{ "lat": 41.22,"lon": -73.34},"Country":"NLD","EventDate":"2017-03-22 16:15:00.0"}
{"index":{"_id":"4"}}
{"Id":"333","Location":{ "lat": 41.22,"lon": -73.34},"Country":"NLD","EventDate":"2017-03-23 15:10:20.0"}
{"index":{"_id":"5"}}
{"Id":"333","Location":{ "lat": 41.22,"lon": -73.34},"Country":"NLD","EventDate":"2017-03-23 16:09:20.0"}

```

So, the average number of events per hour is 1.5 for `15` (because 2 on 2017-03-22 and 1 on 2017-03-23) and 1 for `16`.

How can I do this in Elasticsearch (5.5) My final goal is to create a bar chart in Kibana showing the average count per hours, however I'd prefer to make all calculations in the backend (Elasticsearch).

Currently I tested this query:

```
POST /test/_search
{
    "size": 0,
    "aggs": {
        "my_date_histo":{                
            "date_histogram":{
                "field":"EventDate",
                "interval":"hour"
            },
            "aggs":{
                "the_count":{
                    "value_count" : { "field" : "Id"}
                },
                "the_movavg":{
                    "moving_avg":{ "buckets_path": "the_count" } 
                }
            }
        }
    }
}

```

But it gives me the moving average that is not aggregated by hours:

```
{
  "took": 5,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "my_date_histo": {
      "buckets": [
        {
          "key_as_string": "2017-02-17 15:00:00.000",
          "key": 1487343600000,
          "doc_count": 2,
          "the_count": {
            "value": 2
          }
        },
        {
          "key_as_string": "2017-02-17 16:00:00.000",
          "key": 1487347200000,
          "doc_count": 1,
          "the_count": {
            "value": 1
          },
          "the_movavg": {
            "value": 2
          }
        },
        {
          "key_as_string": "2017-02-17 17:00:00.000",
          "key": 1487350800000,
          "doc_count": 0,
          "the_count": {
            "value": 0
          }
        },
 ....
            {
              "key_as_string": "2017-02-18 15:00:00.000",
              "key": 1487430000000,
              "doc_count": 1,
              "the_count": {
                "value": 1
              },
              "the_movavg": {
                "value": 1.5
              }
            },
            {
              "key_as_string": "2017-02-18 16:00:00.000",
              "key": 1487433600000,
              "doc_count": 1,
              "the_count": {
                "value": 1
              },
              "the_movavg": {
                "value": 1.3333333333333333
              }
            }
          ]
        }
      }
    }

```

---

<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:** [August 21, 2017, 3:48pm UTC](https://discuss.elastic.co/t/cannot-find-the-way-to-calculate-average-count-of-events-per-hour-in-elasticsearch/94356/2 "2017-08-21T15:48:15Z")

</div>

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