# Can ES do a complex aggregation with WHERE and GROUP BY + ORDER BY like in MySQL **URL:** \ **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:** 1\ **Showing post:** 4
**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")
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) --- _[View the full topic](https://discuss.elastic.co/t/can-es-do-a-complex-aggregation-with-where-and-group-by-order-by-like-in-mysql/265559)._