# Sum all first documents

**URL:** <https://discuss.elastic.co/t/sum-all-first-documents/148451>\
**Category:** Kibana\
**Created:** [September 13, 2018, 12:54pm UTC](https://discuss.elastic.co/t/sum-all-first-documents/148451 "2018-09-13T12:54:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![JPelastic](https://avatars.discourse-cdn.com/v4/letter/j/9e8a1a/32.png) [@JPelastic](https://discuss.elastic.co/u/JPelastic)\
**Post date:** [September 13, 2018, 12:54pm UTC](https://discuss.elastic.co/t/sum-all-first-documents/148451/1 "2018-09-13T12:54:15Z")

</div>

My documents are like this:

```auto
{
    "InvoiceNumber" : "12545587",
    "ArticleNumber" : "45521212",
    "LineTotal" : 145.00,
    "InvoiceTotal" : 3200.00
}

```

Each document used to be an invoice line (article is unique per document) on an invoice.  
One of the things I would like to do is this

```auto
Calculate the total value of all invoices in the system.

```

Note that you don't just sum InvoiceTotal, since that field is superfluously added to each article line.

I need to to sum all first (or some other way to select one document per bucket) documents from buckets created by InvoiceNumber.

**Things I tried**

- I tried top\_hits aggregation, but you can't sum on that
- I tried max, but you can't sum on that
- I tried looking at pipelines but I have no idea how that works

---

<div class="post-metadata">

**Author:** ![Aaron\_Caldwell](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aaron_caldwell/32/45755_2.png) [@Aaron\_Caldwell](https://discuss.elastic.co/u/Aaron_Caldwell)\
**Post date:** [September 13, 2018, 7:07pm UTC](https://discuss.elastic.co/t/sum-all-first-documents/148451/2 "2018-09-13T19:07:47Z")

</div>

Hello Johan,

You could try scripted metric aggregations:

[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html)

You should be able to accomplish this on your index using something similar to the following:

```
POST <your_index_name>/_search?size=0
{
    "query" : {
        "match_all" : {}
    },
    "aggs": {
        "invoice_sum": {
            "scripted_metric": {
                "init_script" : "state.invoiceTotals = 0",
                "map_script" : "state.invoiceTotals += doc.InvoiceTotal.value"
            }
        }
    }
}

```

Regards,  
Aaron

---

<div class="post-metadata">

**Author:** ![JPelastic](https://avatars.discourse-cdn.com/v4/letter/j/9e8a1a/32.png) [@JPelastic](https://discuss.elastic.co/u/JPelastic)\
**Post date:** [September 14, 2018, 4:39pm UTC](https://discuss.elastic.co/t/sum-all-first-documents/148451/3 "2018-09-14T16:39:50Z")

</div>

So I took it here

```auto
POST aaa_invoices/_search
{
  "aggs": {
    "invoice_sum": {
      "scripted_metric": {
        "init_script" : "state.invoiceTotals = new ArrayList(); state.MessageNumbers = new ArrayList();",
        "map_script" : "if (state.MessageNumbers.indexOf(doc['enveloppe.Berichtnummer.keyword']) == -1) { state.invoiceTotals.add(doc['totFactBedrag'].get(0)); state.MessageNumbers.add(doc['enveloppe.Berichtnummer.keyword']); }",
        "combine_script": "double invoiceTotal = 0.0; for (i in state.invoiceTotals) { invoiceTotal += i } return [invoiceTotal, state.invoiceTotals, state.MessageNumbers];"
      }
    }
  }
}

```

But this exceeds the max\_result\_window and only gives me the total for 10 invoices, instead of the total of all 40k invoices.

I actually know how I can fix it - just create two indexes, one with invoices and one with invoicelines. But that beats the purpose doesn't it?

---

<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:** [October 12, 2018, 4:40pm UTC](https://discuss.elastic.co/t/sum-all-first-documents/148451/4 "2018-10-12T16:40:07Z")

</div>

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