Sql "case" in elasticsearch

Elastic Stack Elasticsearch
1

I want to do query elastic search with bands(example .. age band {10-20,20-30,etc}). How can i do this .
i have tried painless scripting: by which i am able to create a new field. But i want to do group by , count , sum like SQL operations on ES query result. Please guide me with example .

2

There is Range Query | Elasticsearch Guide [6.6] | Elastic which should do what you want?

Can you elaborate more on what you are trying here. Sharing example code if you have it would be super handy.

3 
SELECT AMOUNT,COUNT(id) AS COUNT_id FROM 
(
SELECT AMOUNT,id, (CASE WHEN AMOUNT < 0 THEN 'MISSING'
                 WHEN AMOUNT >= 0 AND AMOUNT < 2000 THEN  '0-2K' 
                 WHEN AMOUNT >= 2000 AND AMOUNT < 4000 THEN  '2-4K'
				 WHEN AMOUNT >= 4000 AND AMOUNT < 6000 THEN  '4-6K'
				 WHEN AMOUNT >= 20000 AND AMOUNT < 25000 THEN  '20-25K'
				 WHEN AMOUNT >= 25000 AND AMOUNT < 50000 THEN  '25-50K'
				 ELSE  'ABOVE 50K' END) AS AMOUNT
FROM  test
) T
GROUP BY AMOUNT
ORDER BY AMOUNT