# SQL like Group by in Elasticsearch

**URL:** <https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265>\
**Category:** Elasticsearch\
**Created:** [April 24, 2018, 9:01am UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265 "2018-04-24T09:01:37Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Islam\_Elshobokshy](https://avatars.discourse-cdn.com/v4/letter/i/77aa72/32.png) [@Islam\_Elshobokshy](https://discuss.elastic.co/u/Islam_Elshobokshy)\
**Post date:** [April 24, 2018, 9:01am UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265/1 "2018-04-24T09:01:38Z")

</div>

I want to, when filtering my results, at the end, group everything by name for example. If multiple results contain the same name, they are grouped, as well as all of their other fields being summed together if they are numbers.

So I don't just want it to group them by name, I want to get all the results of all the other fields grouped as well depending on that name criteria.

Another example is if I want them grouped by name and country. If they have the same name AND are in the same country, they are grouped.

So how to group them and show all results, and how to group them using more than 1 criteria?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 24, 2018, 9:49am UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265/2 "2018-04-24T09:49:02Z")

</div>

Why not a first terms agg on `name` then a terms agg on `country`?

May be that would work. If not, please share an example of what you have and what ou want to get back.

---

<div class="post-metadata">

**Author:** ![Islam\_Elshobokshy](https://avatars.discourse-cdn.com/v4/letter/i/77aa72/32.png) [@Islam\_Elshobokshy](https://discuss.elastic.co/u/Islam_Elshobokshy)\
**Post date:** [April 24, 2018, 10:01am UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265/3 "2018-04-24T10:01:32Z")

</div>

I am using Elastica. To make it simple let's try with only 1 agg...

I have a lot of filters like this, to filter the search of all my documents :

```auto
$bool_sub = new \Elastica\Query\Bool();
$bool->addMust($bool_sub);
$query->setFilter($bool); 

```

I am adding an aggregation :

```auto
$manufacturers = new Elastica\Aggregation\Terms('name'); 
$manufacturers->setField('name_id');
$query->addAggregation($manufacturers);

```

And then looping over the bucket :

```auto
$bucket = $index->search($query)->getAggregation('name');

```

The bucket only gives me a list of all ids with the doc count of each id but I want all the results in the bucket that matches my filters, grouped by the agg I specified. So I want to show all the results for each grouped item by name, and not only the doc count that shows me how many were grouped... Thus my question.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 24, 2018, 10:50am UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265/4 "2018-04-24T10:50:32Z")

</div>

Can't you add a sub agg on `$manufacturers`?

---

<div class="post-metadata">

**Author:** ![Islam\_Elshobokshy](https://avatars.discourse-cdn.com/v4/letter/i/77aa72/32.png) [@Islam\_Elshobokshy](https://discuss.elastic.co/u/Islam_Elshobokshy)\
**Post date:** [April 24, 2018, 11:48am UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265/5 "2018-04-24T11:48:10Z")

</div>

A sub agg won't give me all of the results, just the result of the agg and the sub aggs. Also it's going to order everything by the aggs and sub aggs which is not what I want. I want to show all of the results of the agg.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 24, 2018, 12:00pm UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265/6 "2018-04-24T12:00:37Z")

</div>

May be I don't understand so an example would help.  
Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

But may be others have an idea.

---

<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:** [May 22, 2018, 12:00pm UTC](https://discuss.elastic.co/t/sql-like-group-by-in-elasticsearch/129265/7 "2018-05-22T12:00:47Z")

</div>

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