Need help with Sum

Elastic Stack Elasticsearch
1

I am new to Elasticsearch. I'm trying to write a query that will group by
a field and calculate a sum. In SQL, my query would look like this: SELECT
lane, SUM(routes) FROM lanes GROUP BY lane

I want to essentially run the same query in ES as I did in SQL, so that my
result would be something like (in json of course): M05: 7103, M03: 9740
I have this data that looks like this in ES:

{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "TUeWFEhnS9q1Ukb2QdZABg"",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M05","routes":4047}
}, {
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "owVmGW9GT562_2Alfru2DA",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M03","routes":4065}
},{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "JY9xNDxqSsajw76oMC2gxA",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M05","routes":3056}
},{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "owVmGW9GT345_2Alfru2DB",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M03","routes":5675}
},...

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

2

As I already mentioned on stackoverflow, you can use terms stats facetshttp://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet/ for
that.

On Tuesday, April 16, 2013 5:07:05 PM UTC-4, OffensivelyBad wrote:

I am new to Elasticsearch. I'm trying to write a query that will group
by a field and calculate a sum. In SQL, my query would look like this: SELECT
lane, SUM(routes) FROM lanes GROUP BY lane

I want to essentially run the same query in ES as I did in SQL, so that my
result would be something like (in json of course): M05: 7103, M03: 9740
I have this data that looks like this in ES:

{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "TUeWFEhnS9q1Ukb2QdZABg"",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M05","routes":4047}
}, {
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "owVmGW9GT562_2Alfru2DA",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M03","routes":4065}
},{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "JY9xNDxqSsajw76oMC2gxA",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M05","routes":3056}
},{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "owVmGW9GT345_2Alfru2DB",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M03","routes":5675}
},...

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

3

I guess you could use statistical facet to achieve the purposes

http://www.elasticsearch.org/guide/reference/api/search/facets/statistical-facet/

{
"query" : {
"match_all" : {}
},
"facets" : {
"sumOfRoutes" : {
"statistical" : {
"field" : "routes"
}
}
}
}

Though the response contains much more then just the sum.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

4

Thanks Igor and gugod. Just what I needed.

On Tuesday, April 16, 2013 5:07:05 PM UTC-4, OffensivelyBad wrote:

I am new to Elasticsearch. I'm trying to write a query that will group
by a field and calculate a sum. In SQL, my query would look like this: SELECT
lane, SUM(routes) FROM lanes GROUP BY lane

I want to essentially run the same query in ES as I did in SQL, so that my
result would be something like (in json of course): M05: 7103, M03: 9740
I have this data that looks like this in ES:

{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "TUeWFEhnS9q1Ukb2QdZABg"",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M05","routes":4047}
}, {
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "owVmGW9GT562_2Alfru2DA",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M03","routes":4065}
},{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "JY9xNDxqSsajw76oMC2gxA",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M05","routes":3056}
},{
"_index" : "kpi",
"_type" : "mroutes_by_lane",
"_id" : "owVmGW9GT345_2Alfru2DB",
"_score" : 1.0, "_source" : {"warehouse_id":107,"date":"2013-04-08","lane" :"M03","routes":5675}
},...

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.