# SORTING, SUM, PAGINATION, and AGGREGATION all in one!

**URL:** <https://discuss.elastic.co/t/sorting-sum-pagination-and-aggregation-all-in-one/291248>\
**Category:** Elasticsearch\
**Created:** [December 8, 2021, 6:04pm UTC](https://discuss.elastic.co/t/sorting-sum-pagination-and-aggregation-all-in-one/291248 "2021-12-08T18:04:42Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![michael\_jaskiewicz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michael_jaskiewicz/32/98514_2.png) [@michael\_jaskiewicz](https://discuss.elastic.co/u/michael_jaskiewicz)\
**Post date:** [December 8, 2021, 6:04pm UTC](https://discuss.elastic.co/t/sorting-sum-pagination-and-aggregation-all-in-one/291248/1 "2021-12-08T18:04:42Z")

</div>

'm using Elasticsearch and I see questions now and then about doing some aggregations with sorting or aggregations with paging, but I never see anything GROUP\_BY, SUM, SORT, and PAGINATION together. If I were to write what I'm looking for as SQL, here it is (without the PAGINATION).

```auto
select invoice_date, address_2, company_name, sum(amount) 
from my_table 
group by invoice_date, address_2, company_name
order by sum(amount) desc

```

I tried doing this using many different techniques like composite aggregation, however it appears I can't do the ORDER\_BY with this on the summation.

```auto
# composite aggregation
POST /746ee3a6-2b87-4288-9f20-3bf3a9e47e93/_search
{
  "size": 0,
  "aggs": {
    "my_buckets": {
      "composite": {
        "sources": [
          { "Address2": { "terms": { "field": "Address2" } } },
          { "Company_Description": { "terms": { "field": "Company_Description" } } },
          { "InvoiceDate": { "terms": { "field": "InvoiceDate" } } }
        ]
      },
      "aggregations": {
        "summation": {
          "sum": { "field": "GrossValue" }
        }
      }
    }
  }
}

```

I tried repeated nested aggregations but I saw a comment somewhere that with many nested levels you can't ORDER\_BY either.

```auto
POST /746ee3a6-2b87-4288-9f20-3bf3a9e47e93/_search
{
  "size":0,
  "from":0,
  "sort":[{"Address2":"asc"}],
  "query":{"bool":{"must":[{"match":{"taxonomy_full_code":-1}}]}},
  "track_total_hits":true,
  "aggs":{
    "agg0":{
      "terms":{"field":"Address2"},
      "aggs":{
        "agg1":{
          "terms":{"field":"Company_Description"},
          "aggs":{
            "agg2":{
              "terms":{"field":"InvoiceDate"},
              "aggs":{"sum(GrossValue)":{"sum":{"field":"GrossValue"}}
              }
            }
          }
        }
      }
    }
  }
}

```

Same with multi-term aggregation

```auto
# multi-term aggregation
POST /746ee3a6-2b87-4288-9f20-3bf3a9e47e93/_search
{
  "size": 0,
  "aggs": {
    "rule_builder": {
      "multi_terms": {
        "terms": [
          {"field": "Address2"},
          {"field": "Company_Description"},
          {"field": "InvoiceDate"}
        ]
      },
      "aggs":{
        "sum(GrossValue)":{"sum":{"field":"GrossValue"}}
      }
    }
  }
}

```

I'm using Elasticsearch 7.14. Is what I'm looking for possible in this version?

---

<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 5, 2022, 6:05pm UTC](https://discuss.elastic.co/t/sorting-sum-pagination-and-aggregation-all-in-one/291248/2 "2022-01-05T18:05:04Z")

</div>

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