# SQL Style GroupBy & OrderBy with function in ES?

**URL:** <https://discuss.elastic.co/t/sql-style-groupby-orderby-with-function-in-es/58845>\
**Category:** Elasticsearch\
**Created:** [August 24, 2016, 4:12pm UTC](https://discuss.elastic.co/t/sql-style-groupby-orderby-with-function-in-es/58845 "2016-08-24T16:12:07Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![madamowski](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/madamowski/32/11397_2.png) [@madamowski](https://discuss.elastic.co/u/madamowski)\
**Post date:** [August 24, 2016, 4:12pm UTC](https://discuss.elastic.co/t/sql-style-groupby-orderby-with-function-in-es/58845/1 "2016-08-24T16:12:07Z")

</div>

Is there a way to Order by ABS(SUM()) in ES 2.3.5?

Looks like I can only do ABS(value) as part of aggregation but not during the order part.

**SQL Example**

```
DECLARE @table TABLE
(
	Name VARCHAR(50),
	Value FLOAT    
)

INSERT INTO @table
SELECT 'First', -15.1
UNION SELECT 'Third', 7.3
UNION SELECT 'Third', -7.3
UNION SELECT 'fourth', -2.4
UNION SELECT 'Second', 12.2

SELECT TOP 3 Name, SUM(Value) [Value] FROM @table
GROUP BY Name 
ORDER BY ABS(SUM(Value)) DESC

```

Result

```
First,-15.1
Second,12.2
Fourth,-2.4

```

**ES Example**

```
POST /_bulk
{ "create": { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "First", "value": -15.1 }
{ "index": { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "Third", "value": 7.3 }
{ "index": { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "Third", "value": -7.3 }
{ "index": { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "fourth", "value": -2.4 }
{ "index": { "_index": "sorttest", "_type": "sortitem" }}
{ "name": "Second", "value": 12.2 }

```

**ES Request**

```
GET /sorttest/_search
{
  "size": 0,
  "aggs": {
	"groupByName": {
	  "terms": {
		"field": "name",
		"size": 3,
		"order": {
		  "sumabsvalue": "desc"
		}
	  },
	  "aggs": {
		"sumvalue": {
		  "sum": {
			"field": "value"
		  }
		},
		"sumabsvalue": {
		  "sum": {
			"field": "value",
			"script": "abs(_value)"
		  }
		}
	  }
	}
  }
}

```

Result

```
First,-15.1
Third,0
Second,12.2

```

Any help appreciated.  
M

---

<div class="post-metadata">

**Author:** ![madamowski](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/madamowski/32/11397_2.png) [@madamowski](https://discuss.elastic.co/u/madamowski)\
**Post date:** [August 26, 2016, 4:20pm UTC](https://discuss.elastic.co/t/sql-style-groupby-orderby-with-function-in-es/58845/2 "2016-08-26T16:20:55Z")

</div>

It's possible to get correct ABS(SUM) values using map reduce in scripted\_metric but unfortunately when trying to order by scripted\_metric aggregation I get the error:

```
"root_cause": [
     {
        "type": "aggregation_execution_exception",
        "reason": "Invalid terms aggregation order path [customsum]. Terms buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
     }
  ]

```

Request:

```
GET /sorttest/_search
{
  "size": 0,
  "aggs": {
	"groupByName": {
	  "terms": {
		"field": "name",
		"size": 4,
		"order": {
		  "customsum": "desc"
		}
	  },
	  "aggs": {
		"sum": {
		  "sum": {
			"field": "value"
		  }
		},
		"sumabsvalue": {
		  "sum": {
			"field": "value",
			"script": "abs(_value)"
		  }
		},
		"customsum": {
		  "scripted_metric": {
			"init_script": "_agg['metric'] = []",
			"map_script": "_agg.metric.add(doc['value'].value)",
			"combine_script": "customsum = 0; for (t in _agg.metric) { customsum += t }; return abs(customsum)",
			"reduce_script": "customsum = 0; for (a in _aggs) { customsum += a }; return customsum"
		  }
		}
	  }
	}
  }
}
```

---

<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:** [July 5, 2017, 10:24pm UTC](https://discuss.elastic.co/t/sql-style-groupby-orderby-with-function-in-es/58845/3 "2017-07-05T22:24:47Z")

</div>