# Sum by quantity and sort by lowest price

**URL:** <https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242>\
**Category:** Elasticsearch\
**Created:** [August 2, 2022, 7:57pm UTC](https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242 "2022-08-02T19:57:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![brampurnot](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brampurnot/32/101233_2.png) [@brampurnot](https://discuss.elastic.co/u/brampurnot)\
**Post date:** [August 2, 2022, 7:57pm UTC](https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242/1 "2022-08-02T19:57:23Z")

</div>

I have a requirement in Elasticsearch which I'm not able to implement at the moment. The use case is as follows; we have certain products uploaded in elastic (1 million + items) and each item has a quantity, a price and a lead time (for delivery).

Now I basically want to get the top matches (based on a product description search) where tot sum of all quantities = 1000 (example) sorted by the lowest price.

A similar but other query would be to get the top 1000 items with the lowest lead time.

Any recommendation on how to implement this and what the most performant way of doing this is?

Assume we have the following records:  
Product 1 | Quantity 200 | price 4USD | lead time 2 days  
Product 2 | Quantity 150 | price 3USD | lead time 5 days  
Product 3 | Quantity 275 | price 5 USD | lead time 14

Now I want to get all products for a maximum of quantity of 200 with the cheapest items first. That would give me something like:  
Product 2  
Product 1

And then it would also give me some aggregates like the average delivery time for these 2 items is 3.5 days and total value is 650USD (150 x 3USD + 50 x 4 USD)

Thanks,  
Bram

---

<div class="post-metadata">

**Author:** ![79g](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/79g/32/109191_2.png) [@79g](https://discuss.elastic.co/u/79g)\
**Post date:** [August 4, 2022, 8:16am UTC](https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242/2 "2022-08-04T08:16:49Z")

</div>

Hello @brampurnot,

try these searches:

For the first query:

```auto
GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ]
}

```

Second query:

```auto
GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ],
  "aggs": {
    "lead_time_avg": {
      "avg": {
        "field": "lead_time"
      }
    },
    "total_price": {
      "sum": {
        "script": {
          "lang": "painless",
          "inline": "doc['price'].value * doc['quantity'].value"
        }
      }
    }
  }
}

```

Used data:

```auto
PUT /discuss
PUT /discuss/_mapping
{
  "properties": {
    "description": {
      "type": "text"
    },
    "quantity": {
      "type": "double"
    },
    "price": {
      "type": "double"
    },
    "lead_time": {
      "type": "double"
    }
  }
}

POST discuss/_doc/
{
  "description": "Product 1",
  "quantity": "200",
  "price": "4",
  "lead_time": "2"
}
POST discuss/_doc/
{
  "description": "Product 2",
  "quantity": "150",
  "price": "3",
  "lead_time": "5"
}
POST discuss/_doc/
{
  "description": "Product 3",
  "quantity": "275",
  "price": "5",
  "lead_time": "14"
}

GET discuss/_search
{
  "query": {
    "range": {
      "quantity": {
        "lte": 200
      }
    }
  },
  "sort": [
    {
      "price": "asc"
    },
    "_score"
  ]
}

```

I attach you also the output:

 ![query](https://us1.discourse-cdn.com/elastic/original/3X/0/9/09f5aeae575ae7e615513a20f9824ed10af7bfc7.png)

Hope it helps 🙂

---

<div class="post-metadata">

**Author:** ![brampurnot](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brampurnot/32/101233_2.png) [@brampurnot](https://discuss.elastic.co/u/brampurnot)\
**Post date:** [August 8, 2022, 9:41am UTC](https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242/3 "2022-08-08T09:41:01Z")

</div>

Thanks for this @79g ! However I don't think this solves my use-case to be honest. I want to make sure that the sum of the "quantity" field doesn't exceed a specific value.

If I run this is my index with more than 300.000 products, then I'm getting back 7000 results because they all have a price that is lower than 200. However it doesn't take into account the sum of the quantity field.

---

<div class="post-metadata">

**Author:** ![79g](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/79g/32/109191_2.png) [@79g](https://discuss.elastic.co/u/79g)\
**Post date:** [August 9, 2022, 6:27am UTC](https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242/4 "2022-08-09T06:27:05Z")

</div>

Hi,

> [@brampurnot](#):
>
> I want to make sure that the sum of the "quantity" field doesn't exceed a specific value.

I did not realize about that 😃  
For that you could add another condition in the search!

---

<div class="post-metadata">

**Author:** ![brampurnot](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brampurnot/32/101233_2.png) [@brampurnot](https://discuss.elastic.co/u/brampurnot)\
**Post date:** [August 9, 2022, 6:55am UTC](https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242/5 "2022-08-09T06:55:12Z")

</div>

Yeah I tried multiple things but doesn't seem to be that simple. This is what I have now:

```auto
{
    "track_scores": true,
    "query": {
      "match_all": {}
    },
    "from": 0,
    "size": 10,
    "sort": [
        {
            "price": "asc"
        },
        "_score"
    ],
    "_source": [
        "description",
        "price",
        "lead_time",
        "quantity"
    ],
    "aggs": {
        "input_parent": {
            "terms": {
                "field": "price",
                "order": {
                    "_key": "asc"
                }
            },
            "aggs": {
                "limited_price": {
                    "scripted_metric": {
                        "init_script": "state['my_hash'] = new HashMap();state['my_hash'].put('sum', 0);state['my_hash'].put('price', 0);state['my_hash'].put('docs', new ArrayList());",
                        "map_script": "if(state['my_hash']['sum'] < 200) {state['my_hash']['sum']+=doc['quantity'].value;state['my_hash']['price']+=doc['price'].value;state['my_hash']['docs'].add(doc['price'].value);}",
                        "combine_script": "return state['my_hash']",
                        "reduce_script": "return states[0]"
                    }
                },
                "limited_quality": {
                    "scripted_metric": {
                        "init_script": "state['my_hash'] = new HashMap();state['my_hash'].put('sum', 0);state['my_hash'].put('quality', 0);",
                        "map_script": "if(state['my_hash']['sum'] < 200) {state['my_hash']['sum']+=doc['quantity'].value;state['my_hash']['quality']+=doc['condition_state'].value;}",
                        "combine_script": "return state['my_hash']",
                        "reduce_script": "return states[0]"
                    }
                },
                "limited_lead": {
                    "scripted_metric": {
                        "init_script": "state['my_hash'] = new HashMap();state['my_hash'].put('sum', 0);state['my_hash'].put('lead_time', 0);",
                        "map_script": "if(state['my_hash']['sum'] < 200) {state['my_hash']['sum']+=doc['quantity'].value;state['my_hash']['lead_time']+=doc['lead_time'].value;}",
                        "combine_script": "return state['my_hash']",
                        "reduce_script": "return states[0]"
                    }
                }
            }
        }
    }
}

```

---

<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:** [September 6, 2022, 6:55am UTC](https://discuss.elastic.co/t/sum-by-quantity-and-sort-by-lowest-price/311242/6 "2022-09-06T06:55:26Z")

</div>

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