# Group by function is not working in sql query in kibana\>dev tools

**URL:** <https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452>\
**Category:** Kibana\
**Created:** [December 23, 2020, 6:46am UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452 "2020-12-23T06:46:26Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![anjali28](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@anjali28](https://discuss.elastic.co/u/anjali28)\
**Post date:** [December 23, 2020, 6:46am UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/1 "2020-12-23T06:46:26Z")

</div>

I am creating a query in kibana\>dev tools.  
Some how my SQL query is not working due to group by function in console.  
The SQL query is working fine in MSSQL management studio.

Query:  
POST /\_sql?format=txt&pretty  
{  
"query": """

```
  Select loginyear,loginmonth,customerId,userId, count(1) customerwiseregularuser
 from
 (
  SELECT customerId,userId,time,
  DATETIME_FORMAT(CAST(time AS DATE), 'YYYY-MM-dd') AS "logindate",
  DATETIME_FORMAT(CAST(time AS DATE), 'MM') AS "loginmonth" ,
  DATETIME_FORMAT(CAST(time AS DATE), 'YYYY') AS "loginyear"
  FROM "isco-regularusers" )a
  GROUP BY loginyear,loginmonth,customerId,userId having count(1) >5
  ORDER BY customerId,userId, customerwiseregularuser
  
  """
}

```

Please help for the error.

 ![query](https://us1.discourse-cdn.com/elastic/original/3X/c/4/c4b6de772d0394d8233854386ace20396476ae4f.png)

---

<div class="post-metadata">

**Author:** ![bhavyarm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bhavyarm/32/22392_2.png) [@bhavyarm](https://discuss.elastic.co/u/bhavyarm)\
**Post date:** [December 23, 2020, 4:50pm UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/2 "2020-12-23T16:50:07Z")

</div>

Hello Anjali,

Kibana by the box doesn't work with SQL.

You will need to use - [https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-jdbc.html](https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-jdbc.html)

Thanks  
Bhavya

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [December 23, 2020, 4:53pm UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/3 "2020-12-23T16:53:47Z")

</div>

Elasticsearch SQL does not support all of the things MSSQL supports - I think in this case you are running into this limitation: [https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#\_using\_a\_sub\_select](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_a_sub_select)

---

<div class="post-metadata">

**Author:** ![anjali28](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@anjali28](https://discuss.elastic.co/u/anjali28)\
**Post date:** [December 23, 2020, 6:56pm UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/4 "2020-12-23T18:56:02Z")

</div>

So how i can achieve this logical query or conditions in kibana??

Thanks

---

<div class="post-metadata">

**Author:** ![anjali28](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@anjali28](https://discuss.elastic.co/u/anjali28)\
**Post date:** [December 23, 2020, 6:57pm UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/5 "2020-12-23T18:57:23Z")

</div>

Okay thanks, so how i can achieve this complex conditions in kibana?

What i have to do in elasticsearch?

Thanks

---

<div class="post-metadata">

**Author:** ![ylasri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ylasri/32/86120_2.png) [@ylasri](https://discuss.elastic.co/u/ylasri)\
**Post date:** [December 23, 2020, 7:13pm UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/6 "2020-12-23T19:13:57Z")

</div>

Try something like this and avoid nested SQL queries

```auto
POST /_sql?format=txt&pretty
{
  "query": """
  SELECT customerId,userId,
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY-MM-dd') AS "logindate",
  DATETIME_FORMAT(CAST(login_date AS DATE), 'MM') AS "loginmonth" ,
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY') AS "loginyear",
  count(*) as login_count
  FROM "users-logon-data"
  GROUP BY 
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY-MM-dd'),
  DATETIME_FORMAT(CAST(login_date AS DATE), 'MM'),
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY'),
  customerId,userId having count(*) >= 1
  order by customerId, userId
  """
}

```

---

<div class="post-metadata">

**Author:** ![anjali28](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@anjali28](https://discuss.elastic.co/u/anjali28)\
**Post date:** [December 24, 2020, 5:26am UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/7 "2020-12-24T05:26:23Z")

</div>

The query i working, but i am not getting the desire the result what need.

I need the count of user who have login more then 5times in different days for the same month.

Requirement to create the query:  
The number of regular users every month, regular users are those who have logged in on 5 different days in the same month (A user could log in several times on the same day, still would count as one).

For this we have the fields which was mentioned in the above query given in the use case.

Thanks

---

<div class="post-metadata">

**Author:** ![anjali28](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@anjali28](https://discuss.elastic.co/u/anjali28)\
**Post date:** [December 24, 2020, 6:05am UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/8 "2020-12-24T06:05:19Z")

</div>

Please guide me to function the jdbc driver use in elasticsearch.

1. how to configure in Elasticsearch  
2.how to excute the query in it.

Thanks

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [January 4, 2021, 7:43am UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/9 "2021-01-04T07:43:17Z")

</div>

> [@anjali28](#):
>
> Please guide me to function the jdbc driver use in elasticsearch.
> 
> 1. how to configure in Elasticsearch  
> 2.how to excute the query in it.

As this is a separate question, please open another topic so people will be able to follow

---

<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:** [February 1, 2021, 7:43am UTC](https://discuss.elastic.co/t/group-by-function-is-not-working-in-sql-query-in-kibana-dev-tools/259452/10 "2021-02-01T07:43:30Z")

</div>

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