# How to fetch the count of the documents from the sql query translate

**URL:** <https://discuss.elastic.co/t/how-to-fetch-the-count-of-the-documents-from-the-sql-query-translate/240394>\
**Category:** Elasticsearch\
**Created:** [July 8, 2020, 4:06pm UTC](https://discuss.elastic.co/t/how-to-fetch-the-count-of-the-documents-from-the-sql-query-translate/240394 "2020-07-08T16:06:46Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jayasri\_Cimba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jayasri_cimba/32/50397_2.png) [@Jayasri\_Cimba](https://discuss.elastic.co/u/Jayasri_Cimba)\
**Post date:** [July 8, 2020, 4:06pm UTC](https://discuss.elastic.co/t/how-to-fetch-the-count-of-the-documents-from-the-sql-query-translate/240394/1 "2020-07-08T16:06:46Z")

</div>

I translate this elastic query sql  
` POST _sql?translate { "query": "SELECT HISTOGRAM(\"@timestamp\", INTERVAL 1 MONTH) AS month, count(DISTINCT(ID)) as id_count, report_period FROM \"indexname-*\" WHERE \"@timestamp\" BETWEEN '2019-01-02T00:00:00.000Z' AND '2020-07-30T00:00:00.000Z' GROUP BY month,report_period" }`

> `{  
> "size" : 0,  
> "query" : {  
> "bool" : {  
> "must" : [  
> {  
> "bool" : {
> 
> ```
> "adjust_pure_negative" : true,
> "boost" : 1.0
> }
> },
> {
> "range" : {
> "@timestamp" : {
> "from" : "2019-01-02T00:00:00.000Z",
> "to" : "2020-07-30T00:00:00.000Z",
> "include_lower" : true,
> "include_upper" : true,
> "boost" : 1.0
> }
> }
> }
> ],
> "adjust_pure_negative" : true,
> "boost" : 1.0
> }
> 
> ```
> 
> },  
> "\_source" : false,  
> "stored\_fields" : "_none_",  
> "aggregations" : {  
> "groupby" : {  
> "composite" : {  
> "size" : 1000,  
> "sources" : [  
> {  
> "ae2cae7b" : {  
> "date\_histogram" : {  
> "field" : "@timestamp",  
> "missing\_bucket" : true,  
> "value\_type" : "date",  
> "order" : "asc",  
> "fixed\_interval" : "2592000000ms",  
> "time\_zone" : "Z"  
> }  
> }  
> },  
> {  
> "4557ce61" : {  
> "terms" : {  
> "field" : "report\_period",  
> "missing\_bucket" : true,  
> "order" : "asc"  
> }
> 
> }  
> }

It generates me the table perfectly with the aggregation , I am trying to write into a dataframe , where I can fetch all the fields present in elastic , but couldnt fetch the id\_count queried from the sql statement.

---

<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:** [August 5, 2020, 4:07pm UTC](https://discuss.elastic.co/t/how-to-fetch-the-count-of-the-documents-from-the-sql-query-translate/240394/2 "2020-08-05T16:07:02Z")

</div>

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