# How elasticsearch query should be written for the following SQL statement?

**URL:** <https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288>\
**Category:** Elasticsearch\
**Created:** [August 22, 2019, 9:44am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288 "2019-08-22T09:44:59Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![mmk1995](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mmk1995](https://discuss.elastic.co/u/mmk1995)\
**Post date:** [August 22, 2019, 9:44am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/1 "2019-08-22T09:44:59Z")

</div>

Hello all,

I have a SQL statement as below:  
`select min(a.datetime),accname,year from CWLC_PC a group by accname,year;`

I have tried many times using agg and max but I cannot get the result as the SQL statement.  
Please help!

Thank you.

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [August 22, 2019, 10:01am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/2 "2019-08-22T10:01:58Z")

</div>

Try this:

```
{
  "aggs": {
    "accname": {
      "terms": {
        "field": "accname"
      },
      "aggs": {
        "year ": {
          "terms": {
            "field": "year "
          },
           "aggs" : {
              "min_date" : { "min" : { "field" : "datetime" } }
          }
        }
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![mmk1995](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mmk1995](https://discuss.elastic.co/u/mmk1995)\
**Post date:** [August 23, 2019, 1:29am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/3 "2019-08-23T01:29:25Z")

</div>

Hello wang,

I have tried your suggestion, but the problem is the result didn't narrow down. What I want is like the SQL statement, the result will actually only showing the min(date) for the particular accname, for example,

> datetime, accname  
> 2018-09-01 abc  
> 2018-09-02 abc

The query result will be

> datetime, accname  
> 2018-09-01 abc

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [August 23, 2019, 1:39am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/4 "2019-08-23T01:39:25Z")

</div>

I think the result will narrow down.

Post test2/\_search

```
{
  "aggs": {
    "accname": {
      "terms": {
        "field": "accname.keyword"
      },
      "aggs": {
        "min_date": {
          "min": {
            "field": "datetime"
          }
        }
      }
    }
  }
}

```

The result is :

```
"aggregations": {
    "accname": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "abc",
          "doc_count": 2,
          "min_date": {
            "value": 1535760000000,
            "value_as_string": "2018-09-01T00:00:00.000Z"
          }
        }
      ]
    }
  }
```

---

<div class="post-metadata">

**Author:** ![mmk1995](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mmk1995](https://discuss.elastic.co/u/mmk1995)\
**Post date:** [August 23, 2019, 1:50am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/5 "2019-08-23T01:50:23Z")

</div>

Hello wang,

After looking at your result, I think it is my elasticsearch problem  
I tried to do a simple query like

> GET /monthly-data/\_search  
> {  
> "query": {  
> "match\_all": {}  
> },  
> "aggregations": {  
> "accname": {  
> "terms": {  
> "field": "User.keyword"  
> }  
> }  
> }  
> }

in my result, it still shows like

> {  
> "took" : 15,  
> "timed\_out" : false,  
> "\_shards" : {  
> "total" : 5,  
> "successful" : 5,  
> "skipped" : 0,  
> "failed" : 0  
> },  
> "hits" : {  
> "total" : 339769,  
> "max\_score" : 1.0,  
> "hits" : [  
> {.....................

But do not have keywords like "aggregation", do you know is that a bug for my version 6.5.4?

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [August 23, 2019, 2:00am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/6 "2019-08-23T02:00:25Z")

</div>

No, the result contains two segments: "hits" and "aggregation" . The "aggregation" segement is in the bottom of result. You can ignore search hits by setting size=0.  
Post test2/search

```
{
      "size": 0,
      "aggs": {
        "accname": {
          "terms": {
            "field": "accname.keyword"
          },
          "aggs": {
            "min_date": {
              "min": {
                "field": "datetime"
              }
            }
          }
        }
      }
    }

```

the complete result :

```
{
  "took": 1010,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 0,
    "hits": [

    ]
  },
  "aggregations": {
    "accname": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "abc",
          "doc_count": 2,
          "min_date": {
            "value": 1535760000000,
            "value_as_string": "2018-09-01T00:00:00.000Z"
          }
        }
      ]
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![mmk1995](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mmk1995](https://discuss.elastic.co/u/mmk1995)\
**Post date:** [August 23, 2019, 2:12am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/7 "2019-08-23T02:12:42Z")

</div>

I see wang,  
Thanks for your help,  
but how can I show all the aggs result? I only see a few aggs results,  
Also, can I use the aggs result to visualize in kibana? how should i configure to visualize the aggs result but not the hits result?

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [August 23, 2019, 2:28am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/8 "2019-08-23T02:28:02Z")

</div>

Yes, the Terms Aggregation doesn't return all results. It returns only the top 20 buckets(maybe 10 buckets, I am not sure) . There are many you should notice about Terms Aggregation, you can refer:[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html)  
The Kibana supports visualize the aggs result.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/a/fa7c976ed6390fb8d009f78c4f9aa2dc3bf18d5c.png)

---

<div class="post-metadata">

**Author:** ![mmk1995](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mmk1995](https://discuss.elastic.co/u/mmk1995)\
**Post date:** [August 23, 2019, 2:48am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/9 "2019-08-23T02:48:13Z")

</div>

but I cannot find min aggregation at where you point at, is that my version 6.5.4 too old?  
sorry that it is kibana question though

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [August 23, 2019, 2:59am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/10 "2019-08-23T02:59:37Z")

</div>

I am using Kibana 6.1.3.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/0/90b66868cbda60e2c09247963be6c13ba25a755e.png)

---

<div class="post-metadata">

**Author:** ![mmk1995](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mmk1995](https://discuss.elastic.co/u/mmk1995)\
**Post date:** [August 23, 2019, 3:16am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/11 "2019-08-23T03:16:36Z")

</div>

I could tell you my goal is that I want to have a cumulative sum overtime on the base on the aggs result, I think I don't want the Y-Axis to be the min(datetime) but cumulative sum instead. Do you know how to do it in one go? Anyway Thanks in answering Kibana questions.

---

<div class="post-metadata">

**Author:** ![wangqinghuan](https://avatars.discourse-cdn.com/v4/letter/w/d26b3c/32.png) [@wangqinghuan](https://discuss.elastic.co/u/wangqinghuan)\
**Post date:** [August 23, 2019, 3:23am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/12 "2019-08-23T03:23:49Z")

</div>

> [@mmk1995](#):
>
> cumulative

You can post it into Kibana category to get more help

---

<div class="post-metadata">

**Author:** ![mmk1995](https://avatars.discourse-cdn.com/v4/letter/m/8797f3/32.png) [@mmk1995](https://discuss.elastic.co/u/mmk1995)\
**Post date:** [August 23, 2019, 3:27am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/13 "2019-08-23T03:27:02Z")

</div>

OK Thanks.

---

<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 20, 2019, 3:27am UTC](https://discuss.elastic.co/t/how-elasticsearch-query-should-be-written-for-the-following-sql-statement/196288/14 "2019-09-20T03:27:03Z")

</div>

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