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.