# Get count by certain field based on a sub query
**URL:** https://discuss.elastic.co/t/get-count-by-certain-field-based-on-a-sub-query/15369
**Category:** Elasticsearch
**Created:** [January 23, 2014, 1:04am UTC](https://discuss.elastic.co/t/get-count-by-certain-field-based-on-a-sub-query/15369 "2014-01-23T01:04:22Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Chen\_Wang](https://discuss.elastic.co/u/Chen_Wang)
#### Post date: [January 23, 2014, 1:04am UTC](https://discuss.elastic.co/t/get-count-by-certain-field-based-on-a-sub-query/15369/1 "2014-01-23T01:04:22Z")
Guys,
I just successfully imported my data to ES, e,g. It has looks like this:
"activity": 'viewed',
"sessionId": "000000143198107b3fe510b041138cd33fdd9252aab9808c",
"campaign\_id":""
,
"activity": 'campaign\_viewed',
"sessionId": "000000143198107b3fe510b041138cd33fdd9252aab9808c",
"campaign\_id":"my\_campaign"
As you can see, the two entries has the same session id, and since the
second entry has a campaign\_id, i will assume the first activity(viewed) is
also generated from the campaign.
So how can i do count like:
(count the activities that are generated from campaign):
count(activity)
where sessionId in (select sessionid from index where
campaign\_id="m\_campaign") ?
Thanks much!
Chen
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/0758af7f-a8ef-451b-a029-e42d1678e73d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0758af7f-a8ef-451b-a029-e42d1678e73d%40googlegroups.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@jpountz](https://discuss.elastic.co/u/jpountz)
#### Post date: [January 23, 2014, 9:23pm UTC](https://discuss.elastic.co/t/get-count-by-certain-field-based-on-a-sub-query/15369/2 "2014-01-23T21:23:30Z")
I don't think this is possible. To me the way to solve this kind of issues
would be to reindex events as soon as you know their campain\_id.
On Thu, Jan 23, 2014 at 2:04 AM, Chen Wang [chen.apache.solr@gmail.com](mailto:chen.apache.solr@gmail.com)wrote:
> Guys,
> I just successfully imported my data to ES, e,g. It has looks like this:
> "activity": 'viewed',
> "sessionId": "000000143198107b3fe510b041138cd33fdd9252aab9808c",
> "campaign\_id":""
> ,
>
> "activity": 'campaign\_viewed',
> "sessionId": "000000143198107b3fe510b041138cd33fdd9252aab9808c",
> "campaign\_id":"my\_campaign"
>
> As you can see, the two entries has the same session id, and since the
> second entry has a campaign\_id, i will assume the first activity(viewed) is
> also generated from the campaign.
> So how can i do count like:
> (count the activities that are generated from campaign):
>
> count(activity)
> where sessionId in (select sessionid from index where
> campaign\_id="m\_campaign") ?
>
> Thanks much!
> Chen
>
> --
> 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/0758af7f-a8ef-451b-a029-e42d1678e73d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0758af7f-a8ef-451b-a029-e42d1678e73d%40googlegroups.com)
> .
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
--
Adrien Grand
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j4mEB0rJGPJOF2bcWHn\_q-MfO0xzD081\_bB2A9%2B52UJww%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j4mEB0rJGPJOF2bcWHn_q-MfO0xzD081_bB2A9%2B52UJww%40mail.gmail.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@Chen\_Wang](https://discuss.elastic.co/u/Chen_Wang)
#### Post date: [January 24, 2014, 8:38pm UTC](https://discuss.elastic.co/t/get-count-by-certain-field-based-on-a-sub-query/15369/3 "2014-01-24T20:38:20Z")
I actually have a feeling that it might be possible by the new aggregation
in ES 1.0, my thoughts are:
1. make the campaign\_id field a integer value
2. group by all session data, doing the count, and also sum the campaign\_id
field.
3. filter through the group with sum(campaign\_id) != 0, (meaning this
session is from campaign), then do a sum on all the filtered group.
But how can I implement this in ES query...
Chen
On Thu, Jan 23, 2014 at 1:23 PM, Adrien Grand \<
[adrien.grand@elasticsearch.com](mailto:adrien.grand@elasticsearch.com)\> wrote:
> I don't think this is possible. To me the way to solve this kind of issues
> would be to reindex events as soon as you know their campain\_id.
>
> On Thu, Jan 23, 2014 at 2:04 AM, Chen Wang [chen.apache.solr@gmail.com](mailto:chen.apache.solr@gmail.com)wrote:
>
> > Guys,
> > I just successfully imported my data to ES, e,g. It has looks like this:
> > "activity": 'viewed',
> > "sessionId": "000000143198107b3fe510b041138cd33fdd9252aab9808c",
> > "campaign\_id":""
> > ,
> >
> > "activity": 'campaign\_viewed',
> > "sessionId": "000000143198107b3fe510b041138cd33fdd9252aab9808c",
> > "campaign\_id":"my\_campaign"
> >
> > As you can see, the two entries has the same session id, and since the
> > second entry has a campaign\_id, i will assume the first activity(viewed) is
> > also generated from the campaign.
> > So how can i do count like:
> > (count the activities that are generated from campaign):
> >
> > count(activity)
> > where sessionId in (select sessionid from index where
> > campaign\_id="m\_campaign") ?
> >
> > Thanks much!
> > Chen
> >
> > --
> > 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](mailto:elasticsearch+unsubscribe@googlegroups.com).
> >
> > To view this discussion on the web visit
> > [https://groups.google.com/d/msgid/elasticsearch/0758af7f-a8ef-451b-a029-e42d1678e73d%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0758af7f-a8ef-451b-a029-e42d1678e73d%40googlegroups.com)
> > .
> > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
>
> --
> Adrien Grand
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "elasticsearch" group.
> To unsubscribe from this topic, visit
> [https://groups.google.com/d/topic/elasticsearch/scwSRLM08vc/unsubscribe](https://groups.google.com/d/topic/elasticsearch/scwSRLM08vc/unsubscribe).
> To unsubscribe from this group and all its topics, send an email to
> [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
> To view this discussion on the web visit
> [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j4mEB0rJGPJOF2bcWHn\_q-MfO0xzD081\_bB2A9%2B52UJww%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j4mEB0rJGPJOF2bcWHn_q-MfO0xzD081_bB2A9%2B52UJww%40mail.gmail.com)
> .
>
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
--
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](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CACim9RnoWHcXtaEp2oCYJ8%2ByAvy0ryDfw\_ey4n6\_eTqYbLmvVQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CACim9RnoWHcXtaEp2oCYJ8%2ByAvy0ryDfw_ey4n6_eTqYbLmvVQ%40mail.gmail.com).
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 1:54am UTC](https://discuss.elastic.co/t/get-count-by-certain-field-based-on-a-sub-query/15369/4 "2017-07-06T01:54:53Z")