# Group by, having sql ..How to Convert

**URL:** <https://discuss.elastic.co/t/group-by-having-sql-how-to-convert/280029>\
**Category:** Elasticsearch\
**Created:** [July 30, 2021, 4:35am UTC](https://discuss.elastic.co/t/group-by-having-sql-how-to-convert/280029 "2021-07-30T04:35:17Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![HYOJIN\_LEE](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyojin_lee/32/78198_2.png) [@HYOJIN\_LEE](https://discuss.elastic.co/u/HYOJIN_LEE)\
**Post date:** [July 30, 2021, 4:35am UTC](https://discuss.elastic.co/t/group-by-having-sql-how-to-convert/280029/1 "2021-07-30T04:35:17Z")

</div>

hello.

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.

**SQL**  
**SELECT CD\_HASH, COUNT(\*)**  
**FROM GRW\_MAIL\_RECEIVER\_LIST**  
**GROUP BY CD\_HASH**  
**HAVING MAX(TM\_RCV) \<\> MIN(TM\_RCV)**

/  
{

```
"size": 0,

"aggs":{

    "group_by_state":{

        "terms": {

            "field": "cd_hash.keyword"

        }

    }

}

```

}  
/

Please help me.. 🤔

---

<div class="post-metadata">

**Author:** ![Wolfram\_Haussig](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wolfram_haussig/32/70528_2.png) [@Wolfram\_Haussig](https://discuss.elastic.co/u/Wolfram_Haussig)\
**Post date:** [July 30, 2021, 6:15am UTC](https://discuss.elastic.co/t/group-by-having-sql-how-to-convert/280029/2 "2021-07-30T06:15:20Z")

</div>

Hi,

Which version and license of ElasticSearch are you using? If you are not using the OpenSource version (the free basic license would be enough), there is a REST API to translate SQLs into ElasticSearch queries: [SQL translate API | Elasticsearch Guide [7.13] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate-api.html)

Best regards  
Wolfram

---

<div class="post-metadata">

**Author:** ![HYOJIN\_LEE](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hyojin_lee/32/78198_2.png) [@HYOJIN\_LEE](https://discuss.elastic.co/u/HYOJIN_LEE)\
**Post date:** [August 10, 2021, 5:31am UTC](https://discuss.elastic.co/t/group-by-having-sql-how-to-convert/280029/3 "2021-08-10T05:31:26Z")

</div>

I could not solve.  
But the information you provided was helpful.  
Thank you 🙂

---

<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:** [September 7, 2021, 5:31am UTC](https://discuss.elastic.co/t/group-by-having-sql-how-to-convert/280029/4 "2021-09-07T05:31:59Z")

</div>

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