# Can ES do a complex aggregation with WHERE and GROUP BY + ORDER BY like in MySQL

**URL:** <https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559>\
**Category:** Elasticsearch\
**Created:** [February 26, 2021, 2:26am UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559 "2021-02-26T02:26:54Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Long\_Quanzheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/long_quanzheng/32/82899_2.png) [@Long\_Quanzheng](https://discuss.elastic.co/u/Long_Quanzheng)\
**Post date:** [February 26, 2021, 2:26am UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/1 "2021-02-26T02:26:54Z")

</div>

Hi, I have a query like this in MySQL that I am thinking to migrate to ElasticSearch for scalability:

```auto
SELELCT userID,sum(countA) as sumA,sum(countB) as sumB
    FROM data_table 
 WHERE groupID=? AND date BETWEEN ? AND ? 
 GROUP BY userID 
 ORDER BY sumA 
LIMIT ?,?

```

For a table of `id, groupID, date, userID, countA, countB` with an index of `<groupID, date>`.

Is it possible to do it in ES? The closest featuer I can find is composite aggregation: [Composite aggregation | Elasticsearch Reference [7.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-composite-aggregation.html)

It seems supporting aggregation(group by), pagination(limit).  
I guess `sum(countA) ` can be done by sub-aggregation?

But it doesn't seem to support filtering by date/groupID.  
Any suggestion?

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [February 26, 2021, 2:36am UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/2 "2021-02-26T02:36:26Z")

</div>

Yes.  
Look at [query dsl](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl.html)  
Specifically, [match](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-match-query.html) for your groupID=?  
[range](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html) for your date between ? AND ?  
[sort](https://www.elastic.co/guide/en/elasticsearch/reference/current/sort-search-results.html) for order by.  
Cheers!

---

<div class="post-metadata">

**Author:** ![Long\_Quanzheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/long_quanzheng/32/82899_2.png) [@Long\_Quanzheng](https://discuss.elastic.co/u/Long_Quanzheng)\
**Post date:** [February 26, 2021, 2:38am UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/3 "2021-02-26T02:38:20Z")

</div>

but it doesn't seem to support `GROUP BY`, did I miss something?  
(Edited the subject of the post to be more clear)

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [February 26, 2021, 2:50am UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/4 "2021-02-26T02:50:08Z")

</div>

you can.  
Look at the following

> [@SQL like GROUP BY AND HAVING](https://discuss.elastic.co/t/sql-like-group-by-and-having/104705):
>
> Hi, I want to achieve some functionality which is available in SQL data stores. I have tried a lot but having a hard time achieving that functionality with elasticsearch. I am successful in creating the queries but the query does not seem to work correctly. I want to do: Group by based on some id Filter out groups with some condition Count the filtered results Tried on Elasticsearch 5.2.2 and 5.6.3 Basically I want to perform query similar to the following SQL: SELECT COUNT(\*) FROM ( S…

> [@Can elasticsearch do GROUP BY multi fields and ORDER BY count?](https://discuss.elastic.co/t/can-elasticsearch-do-group-by-multi-fields-and-order-by-count/176128):
>
> In SQL I would do it possibly like this: SELECT field1,field2, count(\*) as cnt FROM table GROUP BY field1,field2 ORDER BY cnt DESC; Is aggregate query like that possible with ES?

And [Terms Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html)

---

<div class="post-metadata">

**Author:** ![Long\_Quanzheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/long_quanzheng/32/82899_2.png) [@Long\_Quanzheng](https://discuss.elastic.co/u/Long_Quanzheng)\
**Post date:** [February 26, 2021, 5:56am UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/5 "2021-02-26T05:56:56Z")

</div>

HAVING is different from WHERE.

WHERE will do before grouping but HAVING is after.

Is it possible to apply the filtering before the aggregation?

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [March 1, 2021, 12:13am UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/6 "2021-03-01T00:13:28Z")

</div>

@Long_Quanzheng  
All your requirements can be fulfilled with ELK.  
I suggest that you will download, install and configure an instance and have a go.

---

<div class="post-metadata">

**Author:** ![Long\_Quanzheng](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/long_quanzheng/32/82899_2.png) [@Long\_Quanzheng](https://discuss.elastic.co/u/Long_Quanzheng)\
**Post date:** [March 1, 2021, 7:44pm UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/7 "2021-03-01T19:44:50Z")

</div>

Thanks!  
I tested this SQL seems working:

```auto
curl -X GET "localhost:9200/_sql?format=txt&pretty=true" -H 'Content-Type: application/json' -d'
{
  "query" :"SELECT email,sum(countA) FROM \"library\" GROUP BY email LIMIT 2",
  "filter" :{
    "range" :{
       "number":{
         "gte":1,
         "lte":100,
       }
    }
  }
}
'

```

BTW, do you know how can I get the DSL version of this query?  
I am very curious how the aggregation DSL works for this because the documentation doesn't have those feature working together.

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [March 2, 2021, 12:45pm UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/8 "2021-03-02T12:45:13Z")

</div>

Why not:

```auto
curl -X GET "localhost:9200/_sql?format=txt&pretty=true" -H 'Content-Type: application/json' -d'
{
  "query" :"SELECT email,sum(countA) FROM \"library\" WHERE \"number\" between 1 and 100 GROUP BY email LIMIT 2"
}
'

```

and you can use the [sql translate api](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html) to see the query dsl.

---

<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:** [March 30, 2021, 12:45pm UTC](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559/9 "2021-03-30T12:45:36Z")

</div>

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