Elastic SQL COUNT WHEN substitutes?

[Yukinarija]

Hi there,

Spent half of day trying to get the results. I want a simple table something like:

endpoint, 200 count , 200 (%), other count, other (%)
/url1 , 99, 99%, 1, 1%
/url2, 999, 99%, 1, 0.1%

Data is coming in from elk log,thought we would use canvas and tables. The problem is that any SQL we've tried to come up like
SELECT endpoint, COUNT(WHEN RESPONSE = 200 THEN 1.....) it seems is not supported. JOIN as well is also an issue.

Has somebody had luck with something like this? It seems relatively simple but I am having trouble finding something like this :).

Thanks.

[Andrei_Stefan]

@Yukinarija unfortunately, that's not possible at the moment in ES SQL, unless you execute three queries: one for counting the entire set of documents grouped by endpoint, another for counting code 200 documents grouped by endpoint and the third one counting codes that are different than 200 grouped by endpoint.