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

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.

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

Thanks
Bhavya

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

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

Thanks

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

What i have to do in elasticsearch?

Thanks

Try something like this and avoid nested SQL queries

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
  """
}

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

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

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