# Unique feilds count **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [January 13, 2022, 11:44am UTC](https://discuss.elastic.co/t/unique-feilds-count/294268 "2022-01-13T11:44:34Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Naga\_Prudhvi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/naga_prudhvi/32/99501_2.png) [@Naga\_Prudhvi](https://discuss.elastic.co/u/Naga_Prudhvi)\ **Post date:** [January 13, 2022, 11:44am UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/1 "2022-01-13T11:44:34Z")
How can i right DSL query for count of unique value in fields (Colom) for one day. where unique value is greater than 15 SQL example: select /_+parallel(15)_/ fruits ,count(1) from all\_items where LOAD\_DATE=trunc(sysdate) group by fruits having count(1)\>15 ; output : 17-jan-2022 apple 16 orange 18 15-jan-2022 apple 19 orange 22 pineapple 30 ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 13, 2022, 12:49pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/2 "2022-01-13T12:49:58Z")
There are min\_doc\_count parameter in Terms aggregation. > **[Terms aggregation | Elasticsearch Guide \[7.16\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_minimum_document_count_4)** ---
**Author:** ![Naga\_Prudhvi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/naga_prudhvi/32/99501_2.png) [@Naga\_Prudhvi](https://discuss.elastic.co/u/Naga_Prudhvi)\ **Post date:** [January 13, 2022, 1:10pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/3 "2022-01-13T13:10:58Z")
Thanks for the help, tried in DEV tools works and copy pasted DSL query in kibana visual filter by query getting below error [1:440] [bool] failed to parse field [filter] which i tried { "aggs": { "tags": { "terms": { "field": "CALLED\_NUMBER.keyword", "min\_doc\_count": 15 } } } } ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 13, 2022, 1:19pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/4 "2022-01-13T13:19:11Z")
Where did you paste it? it is an aggregation query and not a filter query. ---
**Author:** ![Naga\_Prudhvi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/naga_prudhvi/32/99501_2.png) [@Naga\_Prudhvi](https://discuss.elastic.co/u/Naga_Prudhvi)\ **Post date:** [January 13, 2022, 1:23pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/5 "2022-01-13T13:23:36Z")
i want to apply that dsl query in kibana --\> visuals--\> tables-\> filter-\> filter by dsl query ---
**Author:** ![Naga\_Prudhvi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/naga_prudhvi/32/99501_2.png) [@Naga\_Prudhvi](https://discuss.elastic.co/u/Naga_Prudhvi)\ **Post date:** [January 13, 2022, 1:24pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/6 "2022-01-13T13:24:45Z")
![image](https://us1.discourse-cdn.com/elastic/original/3X/5/7/572e0e89c0053fd5c325178167f30305d8c48a90.png) ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 13, 2022, 1:39pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/7 "2022-01-13T13:39:44Z")
That filter shoud be a query for each document. Aggregation clause must be ignored. If you want to filter only tems with 15 or more documents, you need something like Window function of SQL but there is no such query in Elasticsearch. You may have to take some quite different alternatives depending on your goals. ---
**Author:** ![Naga\_Prudhvi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/naga_prudhvi/32/99501_2.png) [@Naga\_Prudhvi](https://discuss.elastic.co/u/Naga_Prudhvi)\ **Post date:** [January 13, 2022, 1:44pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/8 "2022-01-13T13:44:36Z")
The real scenario is I need a table visual with tables split based on dates that is done, In that table i feild (called\_number) if it is repeting more than 15 times in that day and no of times its repeted count. Can you please help me with that ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [January 13, 2022, 2:08pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/9 "2022-01-13T14:08:45Z")
You could use aggregation-based "Data table" visualization. > **[Aggregation-based | Kibana Guide \[7.16\] | Elastic](https://www.elastic.co/guide/en/kibana/current/add-aggregation-based-visualization-panels.html)** > > Consider the production components that are used to generate reports. An example: ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/5/55e74c6ad6a78b468fbea687f5e460fdbf8122e5.png) ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/8/8876a5ccd0a552afbc0396d3d7f2ae0f3c74161a.png) ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/0/000f6ad5f61f14e076c5cac8c4ee68222a9e50d0.png) You can't hide Count column. ---
**Author:** ![Naga\_Prudhvi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/naga_prudhvi/32/99501_2.png) [@Naga\_Prudhvi](https://discuss.elastic.co/u/Naga_Prudhvi)\ **Post date:** [January 13, 2022, 6:25pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/10 "2022-01-13T18:25:16Z")
Thanks I really appreciate it. It word for my scenario. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 10, 2022, 6:25pm UTC](https://discuss.elastic.co/t/unique-feilds-count/294268/11 "2022-02-10T18:25:36Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.