# Multiple group by

**URL:** <https://discuss.elastic.co/t/multiple-group-by/25479>\
**Category:** Elasticsearch\
**Created:** [July 13, 2015, 8:08pm UTC](https://discuss.elastic.co/t/multiple-group-by/25479 "2015-07-13T20:08:07Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pradeep\_Gowda](https://avatars.discourse-cdn.com/v4/letter/p/ebca7d/32.png) [@Pradeep\_Gowda](https://discuss.elastic.co/u/Pradeep_Gowda)\
**Post date:** [July 13, 2015, 8:08pm UTC](https://discuss.elastic.co/t/multiple-group-by/25479/1 "2015-07-13T20:08:07Z")

</div>

How to write a efficient ES query for the below SQL query?

```
Select sum(visits) visits
from index
group by ip, port
order by visits desc
limit 10

```

I am using below ES query, but this will order the visit inside port bucket. I wan't to get top visits after grouping them by destination ip and port.

```
"aggregations" : {
	"ip": 
	{
		"terms": 
		{
			"field": "ip",
			"size": 10
		},
		"aggregations": 
		{
			"port": 
			{
				"terms": 
				{
					"field": "port",
					"size": 0,
					"order": 
					{
						"visits": "desc"
					}
				},
				"aggregations": 
				{
					"visits": 
					{
						"sum": 
						{
							"field": "visits"
						}
					}
				}
			}
		}
	}
}

```

Can some one help me with this?

Thanks.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [July 13, 2015, 11:41pm UTC](https://discuss.elastic.co/t/multiple-group-by/25479/2 "2015-07-13T23:41:48Z")

</div>

I don't believe you can currently do this in a single query, you will have to do two and then join them externally.

Pipeline aggs in 2.0 should do this though.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [July 14, 2015, 6:48am UTC](https://discuss.elastic.co/t/multiple-group-by/25479/3 "2015-07-14T06:48:54Z")

</div>

Couldn't you use a script in your agg to combine ip and port into a single token for grouping purposes?

---

<div class="post-metadata">

**Author:** ![Pradeep\_Gowda](https://avatars.discourse-cdn.com/v4/letter/p/ebca7d/32.png) [@Pradeep\_Gowda](https://discuss.elastic.co/u/Pradeep_Gowda)\
**Post date:** [July 14, 2015, 7:36am UTC](https://discuss.elastic.co/t/multiple-group-by/25479/4 "2015-07-14T07:36:10Z")

</div>

I was able to do this using script. Below is the solution

```
{
  "size": 0,
  "aggs": {
    "destination": {
      "terms": {
        "script": "doc['ip'].value + ':' + doc['port'].value", "order": {
          "visits": "desc"
        }
      },
				"aggregations": 
				{
					"visits": 
					{
						"sum": 
						{
							"field": "visits"
						}
					
					}
				}
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![Sadok\_Mtir](https://avatars.discourse-cdn.com/v4/letter/s/258eb7/32.png) [@Sadok\_Mtir](https://discuss.elastic.co/u/Sadok_Mtir)\
**Post date:** [August 29, 2016, 12:54pm UTC](https://discuss.elastic.co/t/multiple-group-by/25479/5 "2016-08-29T12:54:05Z")

</div>

As I see you need to nest the sum aggregation in the scope of the port aggregation, then you will get the sum (visits) inside the bucket of ip and port together.

---

<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/multiple-group-by/25479/6 "2017-07-05T22:24:27Z")

</div>