# Drill down Aggregation **URL:** \ **Category:** Elasticsearch\ **Created:** [September 5, 2016, 12:49pm UTC](https://discuss.elastic.co/t/drill-down-aggregation/59811 "2016-09-05T12:49:08Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![ccy](https://avatars.discourse-cdn.com/v4/letter/c/3be4f8/32.png) [@ccy](https://discuss.elastic.co/u/ccy)\ **Post date:** [September 5, 2016, 12:49pm UTC](https://discuss.elastic.co/t/drill-down-aggregation/59811/1 "2016-09-05T12:49:08Z")
Hello, theres I'm new in Elastic. Is it possible to get a drill down result in one query like SQL. Suppose, I have some rainfall data in SQL statement follows. ( same mapping in Elastic) USE tempdb GO DROP TABLE rainfall GO CREATE TABLE rainfall ( raindate SMALLDATETIME, country VARCHAR(10), area VARCHAR(10), city VARCHAR(20), fall DECIMAL(7,2), CONSTRAINT pk\_rainfall PRIMARY KEY ( raindate, country, city ) ) GO INSERT INTO rainfall SELECT '2016-01-01', 'country1','area1', 'city1', 10 INSERT INTO rainfall SELECT '2016-01-02', 'country1','area1', 'city1', 20 INSERT INTO rainfall SELECT '2016-01-03', 'country1','area1', 'city1', 12 INSERT INTO rainfall SELECT '2016-01-04', 'country1','area1', 'city1', 15 INSERT INTO rainfall SELECT '2016-01-05', 'country1','area1', 'city1', 16 INSERT INTO rainfall SELECT '2016-01-01', 'country1','area1', 'city2', 14 INSERT INTO rainfall SELECT '2016-01-02', 'country1','area1', 'city2', 15 INSERT INTO rainfall SELECT '2016-01-03', 'country1','area1', 'city2', 16 INSERT INTO rainfall SELECT '2016-01-04', 'country1','area1', 'city2', 17 INSERT INTO rainfall SELECT '2016-01-05', 'country1','area1', 'city2', 18 INSERT INTO rainfall SELECT '2016-01-01', 'country1','area2', 'city3', 20 INSERT INTO rainfall SELECT '2016-01-02', 'country1','area2', 'city3', 22 INSERT INTO rainfall SELECT '2016-01-03', 'country1','area2', 'city3', 23 INSERT INTO rainfall SELECT '2016-01-04', 'country1','area2', 'city3', 24 INSERT INTO rainfall SELECT '2016-01-05', 'country1','area2', 'city3', 25 SELECT raindate ,r.country ,r.area ,SUM(r.fall) FROM rainfall AS r GROUP BY GROUPING SETS( ( raindate ), ( raindate,country ), (raindate,country, area ), ( )) run above query will get result follows: 2016-01-01 00:00:00 country1 area1 24.00 2016-01-01 00:00:00 country1 area2 20.00 2016-01-01 00:00:00 country1 NULL 44.00 2016-01-01 00:00:00 NULL NULL 44.00 2016-01-02 00:00:00 country1 area1 35.00 2016-01-02 00:00:00 country1 area2 22.00 2016-01-02 00:00:00 country1 NULL 57.00 2016-01-02 00:00:00 NULL NULL 57.00 2016-01-03 00:00:00 country1 area1 28.00 2016-01-03 00:00:00 country1 area2 23.00 2016-01-03 00:00:00 country1 NULL 51.00 2016-01-03 00:00:00 NULL NULL 51.00 2016-01-04 00:00:00 country1 area1 32.00 2016-01-04 00:00:00 country1 area2 24.00 2016-01-04 00:00:00 country1 NULL 56.00 2016-01-04 00:00:00 NULL NULL 56.00 2016-01-05 00:00:00 country1 area1 34.00 2016-01-05 00:00:00 country1 area2 25.00 2016-01-05 00:00:00 country1 NULL 59.00 2016-01-05 00:00:00 NULL NULL 59.00 NULL NULL NULL 267.00 ---
**Author:** ![ccy](https://avatars.discourse-cdn.com/v4/letter/c/3be4f8/32.png) [@ccy](https://discuss.elastic.co/u/ccy)\ **Post date:** [September 6, 2016, 1:34am UTC](https://discuss.elastic.co/t/drill-down-aggregation/59811/2 "2016-09-06T01:34:05Z")
Post Elastic sample data, hope that someone can help #delete index DELETE /sampledata # Index example docs POST /sampledata/rainfall/\_bulk {"index":{}} {"raindate":"2016-01-01T00:00:00","country":"country1","area":"area2","city":"city3","fall":20} {"index":{}} {"raindate":"2016-01-04T00:00:00","country":"country1","area":"area1","city":"city2","fall":17} {"index":{}} {"raindate":"2016-01-05T00:00:00","country":"country1","area":"area1","city":"city1","fall":16} {"index":{}} {"raindate":"2016-01-05T00:00:00","country":"country1","area":"area1","city":"city2","fall":18} {"index":{}} {"raindate":"2016-01-03T00:00:00","country":"country1","area":"area1","city":"city2","fall":16} {"index":{}} {"raindate":"2016-01-03T00:00:00","country":"country1","area":"area2","city":"city3","fall":23} {"index":{}} {"raindate":"2016-01-02T00:00:00","country":"country1","area":"area2","city":"city3","fall":22} {"index":{}} {"raindate":"2016-01-03T00:00:00","country":"country1","area":"area1","city":"city1","fall":12} {"index":{}} {"raindate":"2016-01-05T00:00:00","country":"country1","area":"area2","city":"city3","fall":25} {"index":{}} {"raindate":"2016-01-01T00:00:00","country":"country1","area":"area1","city":"city2","fall":14} {"index":{}} {"raindate":"2016-01-02T00:00:00","country":"country1","area":"area1","city":"city2","fall":15} {"index":{}} {"raindate":"2016-01-01T00:00:00","country":"country1","area":"area1","city":"city1","fall":10} {"index":{}} {"raindate":"2016-01-02T00:00:00","country":"country1","area":"area1","city":"city1","fall":20} {"index":{}} {"raindate":"2016-01-04T00:00:00","country":"country1","area":"area1","city":"city1","fall":15} {"index":{}} {"raindate":"2016-01-04T00:00:00","country":"country1","area":"area2","city":"city3","fall":24} GET /sampledata/rainfall/\_search { } ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [September 6, 2016, 8:35am UTC](https://discuss.elastic.co/t/drill-down-aggregation/59811/3 "2016-09-06T08:35:37Z")
> [@ccy](#): > > Is it possible to get a drill down result in one query like SQL. Yes. Unlike SQL we use JSON for both the query syntax and the results. The structure of the JSON reflect the hierarchies in the expressions. Here is an example below, similar to your question that requests a break-down by date, then a grouping category then finally a sum of a value: ``` GET crunchbase/_search { "size":0, "aggs":{ "by_date":{ "date_histogram":{ "field":"investment_date", "interval":"day" }, "aggs":{ "byCategory":{ "terms":{ "field":"category" }, "aggs":{ "myTotals":{ "sum":{ "field":"investment_amount" } } } } } } } } ``` Note the use of the `date_histogram` to group documents into date-based buckets and the `terms` aggregation to create groups by a term, in this case a category like "solar power". The final `sum` aggregation totals the value of an `investment_amout` field. It should be easy for you to translate this into your rainfall example. The definitive guide [1] is a great learning resource [1] [Aggregation Test-Drive | Elasticsearch: The Definitive Guide [master] | Elastic](https://www.elastic.co/guide/en/elasticsearch/guide/master/_aggregation_test_drive.html) ---
**Author:** ![ccy](https://avatars.discourse-cdn.com/v4/letter/c/3be4f8/32.png) [@ccy](https://discuss.elastic.co/u/ccy)\ **Post date:** [September 6, 2016, 2:36pm UTC](https://discuss.elastic.co/t/drill-down-aggregation/59811/4 "2016-09-06T14:36:58Z")
Many thanks ! But how to add an aggs "byArea" to get the Area total inside the byCountry aggs ? ``` GET /sampledata/rainfall/_search { "size": 0, "aggs": { "byDate": { "date_histogram": { "field": "raindate", "interval": "day" }, "aggs": { "byCountry": { "terms": { "field": "country" }, "aggs": { "countryTotals": { "sum": { "field": "fall" } } } } } } } } ``` ---
**Author:** ![ccy](https://avatars.discourse-cdn.com/v4/letter/c/3be4f8/32.png) [@ccy](https://discuss.elastic.co/u/ccy)\ **Post date:** [September 6, 2016, 2:56pm UTC](https://discuss.elastic.co/t/drill-down-aggregation/59811/5 "2016-09-06T14:56:55Z")
``` I got the answer. But how to add sum bucket to get the Country total ? GET /sampledata/rainfall/_search { "size": 0, "aggs": { "byDate": { "date_histogram": { "field": "raindate", "interval": "day" }, "aggs": { "byCountry": { "terms": { "field": "country", "size": 0 }, "aggs": { "byArea": { "terms": { "field": "area", "size": 0 }, "aggs": { "areaTotal": { "sum": { "field": "fall" } } } } } } } } } } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 10:22pm UTC](https://discuss.elastic.co/t/drill-down-aggregation/59811/6 "2017-07-05T22:22:15Z")