# Count results on filtered and sorted query

**URL:** <https://discuss.elastic.co/t/count-results-on-filtered-and-sorted-query/172568>\
**Category:** Elasticsearch\
**Created:** [March 15, 2019, 4:22pm UTC](https://discuss.elastic.co/t/count-results-on-filtered-and-sorted-query/172568 "2019-03-15T16:22:14Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lowwa](https://avatars.discourse-cdn.com/v4/letter/l/b9bd4f/32.png) [@Lowwa](https://discuss.elastic.co/u/Lowwa)\
**Post date:** [March 15, 2019, 4:22pm UTC](https://discuss.elastic.co/t/count-results-on-filtered-and-sorted-query/172568/1 "2019-03-15T16:22:14Z")

</div>

I'm having troubles doing this equivalent in ES:

```
SELECT COUNT(*)
FROM
(
    SELECT current_place
    FROM `request`
    WHERE user_id = '3'
    ORDER BY asked_at DESC
    LIMIT 10
) sr1
WHERE current_place = '4'

```

The goal is to take the 10 most recent records for an user (`asked_at` is a timestamp field), and count how many record have a `current_place = '4'`

In Elasticsearch I did this, without ordering because I didn't even succeed to filter to 10 elements:

```
GET /index/type/_search
{
  "size": 10,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "user_id": 3
          }
        },
        {
          "term": {
            "current_place": 4
          }
        }
      ]
    }
  }
}

```

Which gives me:

```
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 54,
    "max_score" : 0.0,
    "hits" : [
      ... truncated, 10 records ...
    ]
  }
}

```

How can I perform a count on the ordered and filtered data ?

---

<div class="post-metadata">

**Author:** ![safwan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/safwan/32/31879_2.png) [@safwan](https://discuss.elastic.co/u/safwan)\
**Post date:** [March 15, 2019, 9:10pm UTC](https://discuss.elastic.co/t/count-results-on-filtered-and-sorted-query/172568/2 "2019-03-15T21:10:51Z")

</div>

You must use [`sort`](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-sort.html) for sorting the records according to `asked_at`.

> [@Lowwa](#):
>
> and count how many record have a `current_place = '4'`

In order to get the count, you should run [value count aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-valuecount-aggregation.html).

---

<div class="post-metadata">

**Author:** ![Lowwa](https://avatars.discourse-cdn.com/v4/letter/l/b9bd4f/32.png) [@Lowwa](https://discuss.elastic.co/u/Lowwa)\
**Post date:** [March 18, 2019, 5:10pm UTC](https://discuss.elastic.co/t/count-results-on-filtered-and-sorted-query/172568/3 "2019-03-18T17:10:48Z")

</div>

How to apply the value count aggregation to just the 10 first records ?

---

<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:** [April 15, 2019, 5:21pm UTC](https://discuss.elastic.co/t/count-results-on-filtered-and-sorted-query/172568/4 "2019-04-15T17:21:36Z")

</div>

Closed on April 15, 2019, 5:21pm UTC

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