# Mysql where clause and group by Help

**URL:** <https://discuss.elastic.co/t/mysql-where-clause-and-group-by-help/22463>\
**Category:** Elasticsearch\
**Created:** [March 2, 2015, 10:39am UTC](https://discuss.elastic.co/t/mysql-where-clause-and-group-by-help/22463 "2015-03-02T10:39:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jacques](https://avatars.discourse-cdn.com/v4/letter/j/71e660/32.png) [@Jacques](https://discuss.elastic.co/u/Jacques)\
**Post date:** [March 2, 2015, 10:39am UTC](https://discuss.elastic.co/t/mysql-where-clause-and-group-by-help/22463/1 "2015-03-02T10:39:57Z")

</div>

Good Day,  
We are moving to Elastic search and I am struggle with a elastic search query, I have my mysql query and want to search on where clause by date and service\_id and then do sum and count and a group by

MySQL Query:  
SELECT count(mtsms\_id) as 'SENT', DATE(timestamp) as `mttimestamp`, sum(billing\_units) as 'UNITS',  
SUM(If ((dlr & 1 = 1), 1,0)) as 'DELIVERED',SUM(If ((dlr & 2 = 2), 1,0)) as 'FAILED',MCCMNC, SMSC  
FROM data s  
WHERE DATE(`timestamp`) = '2014-09-01'  
and id=400  
group by DATE(timestamp),mccmnc, smsc

Elastic  
{  
"from" : 0, "size" : 100,  
"fields" : ["mtsms\_id","id","timestamp","dlr","billing\_units","mccmnc","smsc","rdnc","route\_billing"],  
"query" : {  
"range": {  
"dlr\_timestamp": {  
"from": "2014-09-01"  
}  
}  
}  
}

Document:  
"\_id": "360211261",  
"\_score": 1,  
"\_source": {   
"mtsms\_id": 360211261,  
"id": 400,  
"route\_id": 3457,  
"content": "Hello",  
"timestamp": "2014-09-03T15:02:56+02:00",  
"meta": {  
"clid": "conejo:32859363",  
"msg\_id": 358528184,  
"batch\_identifier": "6064523",  
"type": "mtsms",  
"udhl": 0,  
"udl": 151,  
"udbytes": "132.125",  
"mccmnc": "65501",  
"ported": 0,  
"parts": 1,  
"credit": 1150,  
"autotag\_client\_src": "54071176a1226",  
"smsc": "ok\_smsc",  
"status": "DELIVERED"  
},  
"dlr": 681,  
"dlr\_timestamp": "2014-09-03T15:02:56+02:00",  
"billing\_units": 1,  
"core\_mtsms\_answer": "stat:DELIVRD",  
"mccmnc": "65501",  
"smsc": "ok\_smsc",  
"rdnc": 0,  
"route\_billing": 2050

---

<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:** [July 6, 2017, 12:29am UTC](https://discuss.elastic.co/t/mysql-where-clause-and-group-by-help/22463/2 "2017-07-06T00:29:08Z")

</div>