# Query DSL count distinct
**URL:** https://discuss.elastic.co/t/query-dsl-count-distinct/299751
**Category:** Elasticsearch
**Created:** [March 15, 2022, 2:46pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751 "2022-03-15T14:46:03Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@avnere](https://discuss.elastic.co/u/avnere)
#### Post date: [March 15, 2022, 2:46pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/1 "2022-03-15T14:46:03Z")
Hi,
I am looking for precise count results for index with over 70 millions documents.
Equal to this: **SQL** : select count(distinct column) from table;
I already tried Terms-aggregation, composite-aggregation, cardinality-aggregation. But didn't get exact count.
What is the best approach for it?
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [March 15, 2022, 3:03pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/2 "2022-03-15T15:03:47Z")
If you want to count the number of documents, try:
```auto
GET /your-index/_search?track_total_hits=true&size=0
```
---
### Author:  [@avnere](https://discuss.elastic.co/u/avnere)
#### Post date: [March 15, 2022, 3:42pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/3 "2022-03-15T15:42:53Z")
I want to count distinct value of field.
something like: select count(distinct name) from table;
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [March 15, 2022, 3:55pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/4 "2022-03-15T15:55:28Z")
So [terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html) is the way to go.
---
### Author:  [@avnere](https://discuss.elastic.co/u/avnere)
#### Post date: [March 15, 2022, 4:46pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/5 "2022-03-15T16:46:05Z")
The index with over 70 millions documents. I already tried Terms-aggregation, composite-aggregation, cardinality-aggregation. But didn't get exact count.
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [March 15, 2022, 6:03pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/6 "2022-03-15T18:03:34Z")
Could you share the request and the response please? With a `terms` agg.
---
### Author:  [@avnere](https://discuss.elastic.co/u/avnere)
#### Post date: [March 15, 2022, 6:28pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/7 "2022-03-15T18:28:30Z")
```auto
GET abc/_search
{
"aggs":{
"unique_fields": {
"terms": {
"field": "source",
"size": 10000,
"order": {
"_key": "asc"
}
}
}
},
"size": 0
}
```
In order to compare I ran count on each "source" field and saw that the values are not equal.
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [March 15, 2022, 7:11pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/8 "2022-03-15T19:11:24Z")
Could you share the output?
I'd like to see the first part (with some first terms)
---
### Author:  [@avnere](https://discuss.elastic.co/u/avnere)
#### Post date: [March 15, 2022, 8:31pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/9 "2022-03-15T20:31:08Z")
```auto
{
"took" : 2,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : null,
"hits" : []
},
"aggregations" : {
"unique_fields" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "FIFO",
"doc_count" : 13
},
{
"key" : "FW",
"doc_count" : 77757759
},
{
"key" : "HW",
"doc_count" : 12394396
},
{
"key" : "Info",
"doc_count" : 6705
}
]
}
}
}
```
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [March 15, 2022, 10:48pm UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/10 "2022-03-15T22:48:30Z")
The numbers you are seeing are the exact numbers. What makes you think that they are not?
---
### Author:  [@casterQ](https://discuss.elastic.co/u/casterQ)
#### Post date: [March 16, 2022, 2:30am UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/11 "2022-03-16T02:30:58Z")
terms agg, you can use `shard_size`
> **[Terms aggregation | Elasticsearch Guide \[8.1\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.1/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-shard-size)**
cardinality agg, you can use precision\_threshold
[](https://www.elastic.co/guide/en/elasticsearch/reference/8.1/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-shard-size)
---
### Author:  [@avnere](https://discuss.elastic.co/u/avnere)
#### Post date: [March 16, 2022, 7:48am UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/12 "2022-03-16T07:48:18Z")
When running count on specific field I get different number.
For Example:
When running the following I get count 6561, while with terms aggs I got 6705.
```auto
GET baseelements_rwr/_count
{
"query": {
"bool": {
"must": [
{"match": {"source": "Info"}}
]
}
}
}
```
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [March 16, 2022, 7:58am UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/13 "2022-03-16T07:58:03Z")
Cardinality aggregation, like some other aggregation types, are by design approximations. This is generally highligted in the documentation. I recommend reading the explanation and discussion provided [in this thread](https://discuss.elastic.co/t/elasticsearch-aggregation-on-million-or-more-data/293641) as well as [this one](https://discuss.elastic.co/t/are-there-plans-to-support-accurate-distinct/232808).
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [March 16, 2022, 9:48am UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/14 "2022-03-16T09:48:02Z")
Could you try with:
```auto
GET baseelements_rwr/_count
{
"query": {
"bool": {
"must": [
{"term": {"source": "Info"}}
]
}
}
}
```
What is the mapping for this `source` field?
What does a typical document look like?
---
### Author:  [@avnere](https://discuss.elastic.co/u/avnere)
#### Post date: [March 16, 2022, 10:34am UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/15 "2022-03-16T10:34:37Z")
source field is keyword.
Same results when switching to term instead match.
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [March 16, 2022, 11:09am UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/16 "2022-03-16T11:09:35Z")
Could you share a typical document please?
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 13, 2022, 11:09am UTC](https://discuss.elastic.co/t/query-dsl-count-distinct/299751/17 "2022-04-13T11:09:56Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.