inanzzz | Using elasticsearch aggregation to get AVG, MIN, MAX, SUM and COUNT statistics

Some of the elasticsearch aggregations are same as built-in database function such as GROUP BY, AVG, SUM, MIN, COUNT so on. Example below shows us how to use some of them in elasticsearch. For more information, read Aggregations.

Content of our elasticsearch index and database are equal. We have a few fields such as year, price, title so on. to work with.

Example 1

In this example, we're going to get statistics about year and price data.

Elasticsearch

curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "size": 0,
  "aggregations": {
    "average_year": {
      "avg": {
        "field": "year"
      }
    },
    "minimum_price": {
      "min": {
        "field": "price"
      }
    },
    "maximum_price": {
      "max": {
        "field": "price"
      }
    },
    "total_price": {
      "sum": {
        "field": "price"
      }
    },
    "total_processed_records": {
      "value_count": {
        "field": "price"
      }
    }
  }
}'

Result

{
   "took": 2,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 5000,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "average_year": {
         "value": 2007.398
      },
      "minimum_price": {
         "value": 0.5
      },
      "total_price": {
         "value": 14409.989999999974
      },
      "maximum_price": {
         "value": 5.55
      },
      "total_processed_records": {
         "value": 5000
      }
   }
}

MySQL

mysql> SELECT
    -> AVG(year), MIN(price), MAX(price), SUM(price), COUNT(*)
    -> FROM post;
+-----------+------------+------------+------------+----------+
| AVG(year) | MIN(price) | MAX(price) | SUM(price) | COUNT(*) |
+-----------+------------+------------+------------+----------+
|  2007.398 |       0.50 |       5.55 |   14409.99 |     5000 |
+-----------+------------+------------+------------+----------+
1 row in set (0.00 sec)