# Custom Query for Range / Bucket Aggregation

**URL:** <https://discuss.elastic.co/t/custom-query-for-range-bucket-aggregation/262635>\
**Category:** Kibana\
**Created:** [January 29, 2021, 11:35am UTC](https://discuss.elastic.co/t/custom-query-for-range-bucket-aggregation/262635 "2021-01-29T11:35:18Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bhavin.shah](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@bhavin.shah](https://discuss.elastic.co/u/bhavin.shah)\
**Post date:** [January 29, 2021, 11:35am UTC](https://discuss.elastic.co/t/custom-query-for-range-bucket-aggregation/262635/1 "2021-01-29T11:35:19Z")

</div>

Hi,

ELK setup - 3 nodes/ 7.10.0 version

Query :-  
I am trying to create one query for business where below is raw data and expected output as well.

Left side - is raw data and final data is dealer ID wise brk\_amt wise range summed range.  
For example :- Dealer 5 has 3 different clients and they given different different revenues on different days. In the middle table - total revenue is summed and as per business requirement against dealer wise we need list of clients who has given revenue more than 200 and less 200.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/7/d733374f30b5242f91c723942982ac9dfc6f2e47.png)

Attempted query :-

> ```
> POST _sql/?format=txt
> {
> "query":""" select dealer_id , histogram(g,200) from (
> select dealer_id, ent_id , sum(brk_amt) g
> FROM alias_brkg_details
> WHERE trade_date between '2020-11-01' and 
> '2020-11-03' and source_1 in('OWS','TWS')
> and dealer_id = 'AS109504'
> group by dealer_id,ent_id )
> """
> }
> 
> ```

Error :-

> {  
> "error" : {  
> "root\_cause" : [  
> {  
> "type" : "verification\_exception",  
> "reason" : "Found 1 problem\nline 1:21: [histogram(g,200)] needs to be part of the grouping"  
> }  
> ],  
> "type" : "verification\_exception",  
> "reason" : "Found 1 problem\nline 1:21: [histogram(g,200)] needs to be part of the grouping"  
> },  
> "status" : 400  
> }

Your support on the same is highly appreciated.

With Regards  
Bhavin

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [January 30, 2021, 2:29pm UTC](https://discuss.elastic.co/t/custom-query-for-range-bucket-aggregation/262635/2 "2021-01-30T14:29:00Z")

</div>

If you wish a Kibana vis, you could achieve this using Elasticsearch Data Transforms, or Canvas expression language, or TSVB vis or Timelion vis or VEGA vis.

---

<div class="post-metadata">

**Author:** ![bhavin.shah](https://avatars.discourse-cdn.com/v4/letter/b/278dde/32.png) [@bhavin.shah](https://discuss.elastic.co/u/bhavin.shah)\
**Post date:** [February 1, 2021, 4:08am UTC](https://discuss.elastic.co/t/custom-query-for-range-bucket-aggregation/262635/3 "2021-02-01T04:08:02Z")

</div>

Thanks alot Fabien. I will check and let you know if need any support. Regards Bhavin

---

<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:** [March 1, 2021, 4:08am UTC](https://discuss.elastic.co/t/custom-query-for-range-bucket-aggregation/262635/4 "2021-03-01T04:08:49Z")

</div>

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