# Get distinct count on a field by grouping on another field and select all rows with a condition on that count

**URL:** <https://discuss.elastic.co/t/get-distinct-count-on-a-field-by-grouping-on-another-field-and-select-all-rows-with-a-condition-on-that-count/223549>\
**Category:** Elasticsearch\
**Created:** [March 13, 2020, 5:28pm UTC](https://discuss.elastic.co/t/get-distinct-count-on-a-field-by-grouping-on-another-field-and-select-all-rows-with-a-condition-on-that-count/223549 "2020-03-13T17:28:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![elasticdata101](https://avatars.discourse-cdn.com/v4/letter/e/dfb087/32.png) [@elasticdata101](https://discuss.elastic.co/u/elasticdata101)\
**Post date:** [March 13, 2020, 5:28pm UTC](https://discuss.elastic.co/t/get-distinct-count-on-a-field-by-grouping-on-another-field-and-select-all-rows-with-a-condition-on-that-count/223549/1 "2020-03-13T17:28:53Z")

</div>

Hi, I am very new to elastic search and I need to write a query in Elasticsearch which is equivalent to the following SQL query:

SELECT \* FROM table WHERE field1 IN (SELECT field1 FROM table GROUP BY field1 HAVING COUNT(DISTINCT field2) \>=3)

I checked different posts and found some information on grouping and distinct count but not exactly what I am looking. Can anyone help?

Thanks in advance.

---

<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:** [April 10, 2020, 5:28pm UTC](https://discuss.elastic.co/t/get-distinct-count-on-a-field-by-grouping-on-another-field-and-select-all-rows-with-a-condition-on-that-count/223549/2 "2020-04-10T17:28:56Z")

</div>

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