# How to perform sql group by and sum query in elasticsearch

**URL:** <https://discuss.elastic.co/t/how-to-perform-sql-group-by-and-sum-query-in-elasticsearch/107399>\
**Category:** Elasticsearch\
**Created:** [November 13, 2017, 1:28pm UTC](https://discuss.elastic.co/t/how-to-perform-sql-group-by-and-sum-query-in-elasticsearch/107399 "2017-11-13T13:28:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![kanchan](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kanchan](https://discuss.elastic.co/u/kanchan)\
**Post date:** [November 13, 2017, 1:28pm UTC](https://discuss.elastic.co/t/how-to-perform-sql-group-by-and-sum-query-in-elasticsearch/107399/1 "2017-11-13T13:28:58Z")

</div>

Hi Team,  
I want to perform below query in elasticsearch using dev tool in kibana:

select pd.product,sum(wallet) wallet\_2,sum(revenue) revenue\_2,ef.time\_id from entity\_fact ef  
inner join product\_dim pd on pd.product\_id=ef.product\_id  
inner join wallet\_location\_dim wld on ef.wallet\_location\_id=wld.wallet\_location\_id  
inner join entity\_dim ed on ef.entity\_id=ed.entity\_id  
inner join clients\_dim cd on ef.client\_id=cd.client\_id  
inner join sector\_dim sd on ef.sector\_id=sd.sector\_id  
inner join gap\_tox\_dim gtxd on ef.gap\_tox\_id=gtxd.gap\_tox\_id  
inner join rank\_dim rd on ef.rank\_id=rd.rank\_id  
inner join data\_version\_dim dvd on ef.data\_version\_id=dvd.data\_version\_id  
where ef.client\_id=262  
and ef.wallet\_location\_id=5  
and ef.gap\_tox\_id=1  
and ef.time\_id in (20111,20121)  
and ef.data\_version\_id=1  
and ef.sector\_id in(1,2,3,4,6,7)  
and ef.product\_id in(39,1,13,6,2,9,12,14,20,24,29,33,37,38,7,8,3,4,5,10,11,15,16,17,18,19,125,21,22,23,25,26,27,28,30,31,32,34,35,36)  
group by pd.product,ef.time\_id

how can i use aggregate function and term query.

---

<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:** [December 11, 2017, 1:29pm UTC](https://discuss.elastic.co/t/how-to-perform-sql-group-by-and-sum-query-in-elasticsearch/107399/2 "2017-12-11T13:29:01Z")

</div>

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