# Average per day of week aggregation

**URL:** <https://discuss.elastic.co/t/average-per-day-of-week-aggregation/124132>\
**Category:** Elasticsearch\
**Created:** [March 15, 2018, 3:35pm UTC](https://discuss.elastic.co/t/average-per-day-of-week-aggregation/124132 "2018-03-15T15:35:30Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [March 19, 2018, 12:45pm UTC](https://discuss.elastic.co/t/average-per-day-of-week-aggregation/124132/2 "2018-03-19T12:45:55Z")

</div>

You could use [pipeline aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html) for this. Pipeline aggregations are aggregations that work on the result of other aggregations.

In this case, I would first create a weekly histogram inside of your `total_messages_per_day_of_week` aggregation. This will create a bucket per week of data, and the number of buckets in this histogram corresponds to the total number of Mondays, Tuesdays etc.

Next, I would use a [bucket\_script aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html) to divide the total messages per week day by the number of weeks of data. The bucket\_script aggregation can calculate a metric based on the outputs of other aggregations by referring to those aggregations by their paths. It has access to [special paths](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html#_special_paths) like `_count` (which can be used to get the total number of messages) and `_bucket_count` (to get the number of weeks). Dividing one by the other gets you what you want.

Putting it all together, the request would look like this:

```auto
{
  "size": 0,
  "aggs": {
    "messages_per_channel": {
      "terms": {
        "field": "channelId"
      },
      "aggs": {
        "total_messages_per_day_of_week": {
          "terms": {
            "script": {
              "lang": "painless",
              "source": "doc['date'].value.dayOfWeek"
            }
          },
          "aggs": {
            "number_of_weeks": {
              "date_histogram": {
                "field": "date",
                "interval": "week"
              }
            },
            "average_messages_per_day_of_week": {
              "bucket_script": {
                "buckets_path": {
                  "doc_count": "_count",
                  "number_of_weeks": "number_of_weeks._bucket_count"
                },
                "script": "params.doc_count / params.number_of_weeks"
              }
            }
          }
        }
      }
    }
  }
}

```

---

_[View the full topic](https://discuss.elastic.co/t/average-per-day-of-week-aggregation/124132)._