# ElasticSearch equivalent query for SQL group by multiple columns

**URL:** <https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028>\
**Category:** Elasticsearch\
**Created:** [May 17, 2017, 1:44am UTC](https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028 "2017-05-17T01:44:43Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![nkanand](https://avatars.discourse-cdn.com/v4/letter/n/f05b48/32.png) [@nkanand](https://discuss.elastic.co/u/nkanand)\
**Post date:** [May 17, 2017, 1:44am UTC](https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028/1 "2017-05-17T01:44:43Z")

</div>

I have a few million documents with name and version (both of type keyword) as properties in each. What is the equivalent Elastic query for group by name, version?

I have tried the following query:  
{  
"size":0,  
"query": {  
"bool": {  
"filter": {  
"range": {  
"time": {  
"gte": "2017-01-28",  
"lte": "2017-02-28"  
}  
}  
}  
}  
},  
"aggs": {  
"group\_by\_name": {  
"terms": {  
"field": "name"  
},  
"aggs": {  
"group\_by\_version": {  
"terms": {  
"field": "version"  
}  
}  
}  
}  
}

However the results are not same as doing Group by name, version. The results are grouped by name and within each group, they are grouped by version. How do I modify the above query to group by name, version tuple and return results in descending order?

{  
"took": 1424,  
"timed\_out": false,  
"\_shards": {  
"total": 5,  
"successful": 5,  
"failed": 0  
},  
"hits": {  
"total": 115,  
"max\_score": 0,  
"hits": []  
},  
"aggregations": {  
"group\_by\_name": {  
"doc\_count\_error\_upper\_bound": 2,  
"sum\_other\_doc\_count": 115,  
"buckets": [  
{  
"key": "product1",  
"doc\_count": 50,  
"group\_by\_version": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 50,  
"buckets": [  
{  
"key": "1.0",  
"doc\_count": 40  
},  
{  
"key": "2.0",  
"doc\_count": 10  
},  
]  
}  
},  
{  
"key": "product3",  
"doc\_count": 35,  
"group\_by\_version": {  
"doc\_count\_error\_upper\_bound": 4,  
"sum\_other\_doc\_count": 35,  
"buckets": [  
{  
"key": "8.0",  
"doc\_count": 20  
},  
{  
"key": "9.0",  
"doc\_count": 15  
}  
]  
}  
},  
{  
"key": "product2",  
"doc\_count": 30,  
"group\_by\_version": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 30,  
"buckets": [  
{  
"key": "4.0",  
"doc\_count": 25  
},  
{  
"key": "5.0",  
"doc\_count": 5  
}  
]  
}  
}  
]  
}  
}  
}

**What i want is:**

name, version count  
product1 1.0 40  
product2 4.0 25  
product3 8.0 20  
product3 9.0 15  
product1 2.0 10  
product2 5.0 5

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [May 17, 2017, 4:24am UTC](https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028/2 "2017-05-17T04:24:35Z")

</div>

Ideally you should create a composite field at index time and then run a terms agg on it.

If you can't reindex then use a script to combine 2 fields but that will be slow.

---

<div class="post-metadata">

**Author:** ![nkanand](https://avatars.discourse-cdn.com/v4/letter/n/f05b48/32.png) [@nkanand](https://discuss.elastic.co/u/nkanand)\
**Post date:** [May 18, 2017, 7:10pm UTC](https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028/3 "2017-05-18T19:10:26Z")

</div>

Thanks for the reply. Due to space considerations (we have 20 Billion records), i am really not considering composite field solution.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [May 18, 2017, 7:25pm UTC](https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028/4 "2017-05-18T19:25:36Z")

</div>

You are going to pay a huge price at search time then with a script.

---

<div class="post-metadata">

**Author:** ![nkanand](https://avatars.discourse-cdn.com/v4/letter/n/f05b48/32.png) [@nkanand](https://discuss.elastic.co/u/nkanand)\
**Post date:** [May 19, 2017, 6:08pm UTC](https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028/5 "2017-05-19T18:08:15Z")

</div>

Hi, You are correct. While ElasticSearch solves most of our problems, it falls short on this one. To get maximum benefits out of ES, we'd probably change our problem (no group by multiple columns)

---

<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:** [June 16, 2017, 6:09pm UTC](https://discuss.elastic.co/t/elasticsearch-equivalent-query-for-sql-group-by-multiple-columns/86028/6 "2017-06-16T18:09:17Z")

</div>

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