# Sql "case" in elasticsearch

**URL:** <https://discuss.elastic.co/t/sql-case-in-elasticsearch/166348>\
**Category:** Elasticsearch\
**Created:** [January 30, 2019, 12:11pm UTC](https://discuss.elastic.co/t/sql-case-in-elasticsearch/166348 "2019-01-30T12:11:42Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![examin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/examin/32/70688_2.png) [@examin](https://discuss.elastic.co/u/examin)\
**Post date:** [January 30, 2019, 12:11pm UTC](https://discuss.elastic.co/t/sql-case-in-elasticsearch/166348/1 "2019-01-30T12:11:42Z")

</div>

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 .

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [January 30, 2019, 12:13pm UTC](https://discuss.elastic.co/t/sql-case-in-elasticsearch/166348/2 "2019-01-30T12:13:19Z")

</div>

There is [Range Query | Elasticsearch Guide [6.6] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/6.6/query-dsl-range-query.html) which should do what you want?

> [@examin](#):
>
> i have tried painless scripting: by which i am able to create a new field

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

---

<div class="post-metadata">

**Author:** ![examin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/examin/32/70688_2.png) [@examin](https://discuss.elastic.co/u/examin)\
**Post date:** [January 30, 2019, 12:22pm UTC](https://discuss.elastic.co/t/sql-case-in-elasticsearch/166348/3 "2019-01-30T12:22:58Z")

</div>

```
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
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 27, 2019, 12:23pm UTC](https://discuss.elastic.co/t/sql-case-in-elasticsearch/166348/4 "2019-02-27T12:23:01Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.