# Count distinct value by date **URL:** \ **Category:** Elasticsearch\ **Created:** [June 10, 2013, 1:47pm UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343 "2013-06-10T13:47:36Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![shammes](https://avatars.discourse-cdn.com/v4/letter/s/ea666f/32.png) [@shammes](https://discuss.elastic.co/u/shammes)\ **Post date:** [June 10, 2013, 1:47pm UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343/1 "2013-06-10T13:47:36Z")
I use ElasticSearch for statistical purposes and have recently switched from MySQL to ElasticSearch. My table looks as follows: ## datetime | unique\_identifier | some more fields... 2013-05-01 | abc | ... 2013-05-01 | cde | ... 2013-05-01 | abc | ... 2013-05-01 | abc | ... 2013-05-01 | cde | ... 2013-05-01 | cde | ... 2013-05-01 | abc | ... 2013-05-01 | xyz | ... 2013-05-01 | abc | ... 2013-05-02 | abc | ... 2013-05-02 | cde | ... 2013-05-02 | abc | ... 2013-05-02 | abc | ... 2013-05-02 | cde | ... 2013-05-03 | cde | ... 2013-05-03 | abc | ... 2013-05-03 | xyz | ... 2013-05-03 | abc | ... 2013-05-04 | abc | ... Now I would like to have listed who many different unique\_identifier per day are in the table. Taking the above table as an example, the result would look like this: 2013-05-01 | 3 2013-05-02 | 2 2013-05-03 | 3 2013-05-04 | 1 For this I have always used the following MySQL query: SELECT DATE (datetime), count (distinct unique\_identifier) FROM tablenname GROUP BY DATE(datetime); Unfortunately I could not find the right companion piece to it in ElasticSearch. Can someone give me a hint? Thank you very much Sebastian ---
**Author:** ![Remy\_Turpin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/remy_turpin/32/2277_2.png) [@Remy\_Turpin](https://discuss.elastic.co/u/Remy_Turpin)\ **Post date:** [June 11, 2013, 8:53am UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343/2 "2013-06-11T08:53:05Z")
Hello, You must use date histogram facet : > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. { "query" : { "match\_all" : {} }, "facets" : { "histo1" : { "date\_histogram" : { "field" : "datetime", "interval" : "day" } } } } You can set other interval, by hour, month, week, etc. Le lundi 10 juin 2013 15:47:36 UTC+2, shammes a écrit : > I use Elasticsearch for statistical purposes and have recently switched > from > MySQL to Elasticsearch. My table looks as follows: > > ## datetime | unique\_identifier | some more fields... > > 2013-05-01 | abc | ... > 2013-05-01 | cde | ... > 2013-05-01 | abc | ... > 2013-05-01 | abc | ... > 2013-05-01 | cde | ... > 2013-05-01 | cde | ... > 2013-05-01 | abc | ... > 2013-05-01 | xyz | ... > 2013-05-01 | abc | ... > 2013-05-02 | abc | ... > 2013-05-02 | cde | ... > 2013-05-02 | abc | ... > 2013-05-02 | abc | ... > 2013-05-02 | cde | ... > 2013-05-03 | cde | ... > 2013-05-03 | abc | ... > 2013-05-03 | xyz | ... > 2013-05-03 | abc | ... > 2013-05-04 | abc | ... > > Now I would like to have listed who many different unique\_identifier per > day > are in the table. Taking the above table as an example, the result would > look like this: > > 2013-05-01 | 3 > 2013-05-02 | 2 > 2013-05-03 | 3 > 2013-05-04 | 1 > > For this I have always used the following MySQL query: > SELECT DATE (datetime), count (distinct unique\_identifier) > FROM tablenname > GROUP BY DATE(datetime); > > Unfortunately I could not find the right companion piece to it in > Elasticsearch. > Can someone give me a hint? > > Thank you very much > Sebastian > > -- > View this message in context: > [http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320.html](http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![shammes](https://avatars.discourse-cdn.com/v4/letter/s/ea666f/32.png) [@shammes](https://discuss.elastic.co/u/shammes)\ **Post date:** [June 11, 2013, 2:03pm UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343/3 "2013-06-11T14:03:58Z")
Hi, thanks for your reply. I know the date\_histogram-facet, but this only counts (for example per day) the number of entries or when you set the "value\_field" the numeric value of this field. But i need a distinct count-value. In my example i need the total, how many different "unique\_identifier" per day exists. Your code snippet would have following result: 2013-05-01 | 9 2013-05-02 | 5 2013-05-03 | 4 2013-05-04 | 1 The result i am looking for should look like: 2013-05-01 | 3 2013-05-02 | 2 2013-05-03 | 3 2013-05-04 | 1 Do you have any other hint for me? ---
**Author:** ![Remy\_Turpin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/remy_turpin/32/2277_2.png) [@Remy\_Turpin](https://discuss.elastic.co/u/Remy_Turpin)\ **Post date:** [June 11, 2013, 3:38pm UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343/4 "2013-06-11T15:38:38Z")
Oh OK sorry, I think I have the same problem. I'm interesting by the reply. Le mardi 11 juin 2013 16:03:59 UTC+2, shammes a écrit : > Hi, > > thanks for your reply. > I know the date\_histogram-facet, but this only counts (for example per > day) > the number of entries or when you set the "value\_field" the numeric value > of > this field. > > But i need a distinct count-value. In my example i need the total, how > many > different "unique\_identifier" per day exists. > > Your code snippet would have following result: > 2013-05-01 | 9 > 2013-05-02 | 5 > 2013-05-03 | 4 > 2013-05-04 | 1 > > The result i am looking for should look like: > 2013-05-01 | 3 > 2013-05-02 | 2 > 2013-05-03 | 3 > 2013-05-04 | 1 > > Do you have any other hint for me? > > -- > View this message in context: > [http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320p4036361.html](http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320p4036361.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![q42jaap](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/q42jaap/32/2241_2.png) [@q42jaap](https://discuss.elastic.co/u/q42jaap)\ **Post date:** [June 11, 2013, 9:01pm UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343/5 "2013-06-11T21:01:35Z")
Hi Shammes, In 1.0 there might be some changes to the facet system that allows to nest facets. I think > **[GitHub - bleskes/elasticfacets: A set of facets and related tools for...](https://github.com/bleskes/elasticfacets#faceted-date-histogram)** > > A set of facets and related tools for ElasticSearch - GitHub - bleskes/elasticfacets: A set of facets and related tools for ElasticSearch might be something to look in to, however, the plugin is not compatible with 0.90, so it might be difficult to get it to work. For now I don't see how to do this, but maybe Boaz can explain it better? Jaap On Tuesday, June 11, 2013 5:38:38 PM UTC+2, Rémy Turpin wrote: > Oh OK sorry, I think I have the same problem. > I'm interesting by the reply. > > Le mardi 11 juin 2013 16:03:59 UTC+2, shammes a écrit : > > > Hi, > > > > thanks for your reply. > > I know the date\_histogram-facet, but this only counts (for example per > > day) > > the number of entries or when you set the "value\_field" the numeric value > > of > > this field. > > > > But i need a distinct count-value. In my example i need the total, how > > many > > different "unique\_identifier" per day exists. > > > > Your code snippet would have following result: > > 2013-05-01 | 9 > > 2013-05-02 | 5 > > 2013-05-03 | 4 > > 2013-05-04 | 1 > > > > The result i am looking for should look like: > > 2013-05-01 | 3 > > 2013-05-02 | 2 > > 2013-05-03 | 3 > > 2013-05-04 | 1 > > > > Do you have any other hint for me? > > > > -- > > View this message in context: > > [http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320p4036361.html](http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320p4036361.html) > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Boaz\_Leskes](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/boaz_leskes/32/723_2.png) [@Boaz\_Leskes](https://discuss.elastic.co/u/Boaz_Leskes)\ **Post date:** [June 12, 2013, 11:49am UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343/6 "2013-06-12T11:49:36Z")
Hi Shammes, Remy, Jaap, You could indeed use the faceted-date-histogram with an inlined term facet (with size=0 or 1) to get that information. The faceted dated histogram allows you to first group posting by date and then apply an arbitrary facet to every group. As Jaap already pointed out, the plugin is not compatible with version 0.90.0 and up of Elasticsearch. Version 0.90.0 came with a complete re-write of the internal in memory data structures that drive the faceting engine. That rewrite delivered a tremendous amount of memory savings so I highly recommend using it. Sadly, it also rendered my plugin to be incompatible and I simply didn't have the time to re-write things. If enough people need it, I might find some time to do it and make a 0.90.X compatible version (dropping other features like the hashed terms facet, which is simply incompatible). Of course, pull requests are welcome 🙂 About the 1.0 version of ES - we are currently working on a new powerful faceting engine that will allow to do this and much more by allow to nest facets. It will take a couple of month, though, before it's ready. Cheers, Boaz On Tuesday, June 11, 2013 11:01:35 PM UTC+2, Jaap Taal wrote: > Hi Shammes, > > In 1.0 there might be some changes to the facet system that allows to nest > facets. I think > [GitHub - bleskes/elasticfacets: A set of facets and related tools for ElasticSearch](https://github.com/bleskes/elasticfacets#faceted-date-histogram) > might be something to look in to, however, the plugin is not compatible > with 0.90, so it might be difficult to get it to work. > > For now I don't see how to do this, but maybe Boaz can explain it better? > > Jaap > > On Tuesday, June 11, 2013 5:38:38 PM UTC+2, Rémy Turpin wrote: > > > Oh OK sorry, I think I have the same problem. > > I'm interesting by the reply. > > > > Le mardi 11 juin 2013 16:03:59 UTC+2, shammes a écrit : > > > > > Hi, > > > > > > thanks for your reply. > > > I know the date\_histogram-facet, but this only counts (for example per > > > day) > > > the number of entries or when you set the "value\_field" the numeric > > > value of > > > this field. > > > > > > But i need a distinct count-value. In my example i need the total, how > > > many > > > different "unique\_identifier" per day exists. > > > > > > Your code snippet would have following result: > > > 2013-05-01 | 9 > > > 2013-05-02 | 5 > > > 2013-05-03 | 4 > > > 2013-05-04 | 1 > > > > > > The result i am looking for should look like: > > > 2013-05-01 | 3 > > > 2013-05-02 | 2 > > > 2013-05-03 | 3 > > > 2013-05-04 | 1 > > > > > > Do you have any other hint for me? > > > > > > -- > > > View this message in context: > > > [http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320p4036361.html](http://elasticsearch-users.115913.n3.nabble.com/Count-distinct-value-by-date-tp4036320p4036361.html) > > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). -- 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). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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, 2:31am UTC](https://discuss.elastic.co/t/count-distinct-value-by-date/12343/7 "2017-07-06T02:31:37Z")