# How to query with GROUP BY and COUNT(\*)

**URL:** https://discuss.elastic.co/t/how-to-query-with-group-by-and-count/318936
**Category:** Elasticsearch
**Created:** [November 15, 2022, 8:20am UTC](https://discuss.elastic.co/t/how-to-query-with-group-by-and-count/318936 "2022-11-15T08:20:29Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![Remon\_Andrew](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/remon_andrew/32/99407_2.png) [@Remon\_Andrew](https://discuss.elastic.co/u/Remon_Andrew)
#### Post date: [November 15, 2022, 8:20am UTC](https://discuss.elastic.co/t/how-to-query-with-group-by-and-count/318936/1 "2022-11-15T08:20:29Z")

</div>

Hi all,

I have this query that I used to run on MySQL:  
`SELECT from_mobile_number, event_name, COUNT(*) AS count FROM analytics_events WHERE date >= '2022-10-01 00:00:00' AND date <= '2022-09-30 23:59:59' AND from_mobile_number != 'N/A' GROUP BY from_mobile_number, event_name;`

Now the table is moved to Elasticsearch, and I need an equivalent way to run it  
I have this query, but I still don’t know how to return the `COUNT(*) AS count` using it:

```auto
{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "bool": {
                        "must_not": [
                            {
                                "term": {
                                    "from_mobile_number.keyword": {
                                        "value": "N/A"
                                    }
                                }
                            }
                        ],
                        "boost": 1.0
                    }
                },
                {
                    "range": {
                        "date": {
                            "gte": "2022-10-01T00:00:00.000+04:00",
                            "lte": "2022-10-31T23:59:59.000+04:00",
                            "time_zone": "Z",
                            "boost": 1.0
                        }
                    }
                }
            ],
            "boost": 1.0
        }
    },
    "_source": ["from_mobile_number", "event_name"],
    "aggs": {
        "agg1": {
            "terms": {
                "field": "from_mobile_number.keyword"
            },
            "aggs": {
                "agg2": {
                    "terms": {
                        "field": "event_name.keyword"
                    }
                }
            }
        }
    }
}

```

I need your comments on it—is it the correct way? and how can I return the "COUNT(\*) AS count"

Thanks in advance

---

<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 13, 2022, 8:20am UTC](https://discuss.elastic.co/t/how-to-query-with-group-by-and-count/318936/2 "2022-12-13T08:20:43Z")

</div>

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