SQL like GROUP BY AND HAVING example

Question

I am new to ES and i would need to make a query

Select value 
from table 
group by value 
having count(distinct(id)) > 1

I do not even have areas to start. referencing this SQL like GROUP BY AND HAVING didnt help much.

Example of Data

------------
Value | id |
------------
val1  | 2  |
val2  | 2  |
val3  | 2  |
val1  | 3  |
------------

Expected returns

----
val1 
----

Answer 1

try this

SELECT value
FROM table
GROUP BY value
HAVING COUNT(*) > 1;

Check this link

Answer 2

Since you are new to Elasticsearch and, also, looking into SQL, I would suggest having a look at Elasticsearch-SQL. Documentation is here.

Regarding your specific query, you can try the translate API which will give you the Elasticsearch query it will run for a given SQL query. This can be a great starting point for your final Elasticsearch query. Or you can use ES-SQL as is with the _sql endpoint to send SQL queries and get back results.

Answer 3

try this.

 Select value 
    from table 
    group by value 
    having count(distinct value) > 1

Answer 4

You should be asserting COUNT(value) or COUNT(*) here:

SELECT value
FROM yourTable
GROUP BY value
HAVING COUNT(*) > 1;

The problem with asserting COUNT(DISTINCT value) > 1 is that this condition will never be true. The reason why it will never be true is that after GROUP BY finishes, each group will consist of one and only one Value.