# Sbuqueries and distinct counts
**URL:** https://discuss.elastic.co/t/sbuqueries-and-distinct-counts/16915
**Category:** Elasticsearch
**Created:** [April 9, 2014, 11:59pm UTC](https://discuss.elastic.co/t/sbuqueries-and-distinct-counts/16915 "2014-04-09T23:59:43Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Tim\_Uckun](https://discuss.elastic.co/u/Tim_Uckun)
#### Post date: [April 9, 2014, 11:59pm UTC](https://discuss.elastic.co/t/sbuqueries-and-distinct-counts/16915/1 "2014-04-09T23:59:43Z")
I want to do something like this.
select date\_trunc('month', time\_stamp), sum(distinct\_count) from (
select date\_trunc('week', time\_stamp) as time\_stamp, count(distinct
field\_name) as distinct\_count
from blah
group by date\_trun('week', time\_stamp)
)
group by date\_trunc('month', time\_stamp)
So basically I want to break up the data into weekly chunks and count the
distinct appearances of a value and then sum those up on a per monthly
basis.
In preparation for that I tried to do the subquery for that which looks
like this
aggregations: {
by\_month: {
date\_histogram: {
field: "time\_stamp",
interval: "1M",
format: "yyyy-MM-dd HH:mm"
},
aggregations: {
by\_node\_mac: {
terms: {
field: "node\_mac"
},
aggregations: {
cardinality: {field: 'device\_mac'}
}
```
}
}
}
}
```
}
but I seem to be getting the wrong answers. I am using fake data which
should give me very low numbers for the cardinality but it actually seems
to be counting the number of rows not the number of distinct items. The
numbers are outrageously high.
I tried a precision threshold of 1000 and 100 but it seems to make no
difference.
--
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/4cafbefc-bf00-49aa-9c7c-2240c4f1fd55%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/4cafbefc-bf00-49aa-9c7c-2240c4f1fd55%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Tim\_Uckun](https://discuss.elastic.co/u/Tim_Uckun)
#### Post date: [April 10, 2014, 10:45pm UTC](https://discuss.elastic.co/t/sbuqueries-and-distinct-counts/16915/2 "2014-04-10T22:45:24Z")
I hate to bump myself but does anybody have any input on this at all?
On Thursday, April 10, 2014 11:59:43 AM UTC+12, Tim Uckun wrote:
> I want to do something like this.
>
> select date\_trunc('month', time\_stamp), sum(distinct\_count) from (
> select date\_trunc('week', time\_stamp) as time\_stamp, count(distinct
> field\_name) as distinct\_count
> from blah
> group by date\_trun('week', time\_stamp)
> )
> group by date\_trunc('month', time\_stamp)
>
> So basically I want to break up the data into weekly chunks and count the
> distinct appearances of a value and then sum those up on a per monthly
> basis.
>
> In preparation for that I tried to do the subquery for that which looks
> like this
>
> aggregations: {
> by\_month: {
> date\_histogram: {
> field: "time\_stamp",
> interval: "1M",
> format: "yyyy-MM-dd HH:mm"
> },
> aggregations: {
> by\_node\_mac: {
> terms: {
> field: "node\_mac"
> },
> aggregations: {
> cardinality: {field: 'device\_mac'}
> }
>
> ```
> }
> }
> }
> }
>
> ```
>
> }
>
> but I seem to be getting the wrong answers. I am using fake data which
> should give me very low numbers for the cardinality but it actually seems
> to be counting the number of rows not the number of distinct items. The
> numbers are outrageously high.
>
> I tried a precision threshold of 1000 and 100 but it seems to make no
> difference.
--
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/016b94f6-4b62-4009-83ec-ccef3bfb17f6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/016b94f6-4b62-4009-83ec-ccef3bfb17f6%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [April 21, 2014, 11:42am UTC](https://discuss.elastic.co/t/sbuqueries-and-distinct-counts/16915/3 "2014-04-21T11:42:33Z")
Hey,
you may want to provide a gist with some sample data, so people can
follow.. Also you might need to use the value\_count aggregation somewhere,
to make sure you are only using the counts, but this really depends on your
data model and the data being indexed, so full gist might help a lot.
--Alex
On Fri, Apr 11, 2014 at 12:45 AM, Tim Uckun [timuckun@gmail.com](mailto:timuckun@gmail.com) wrote:
> I hate to bump myself but does anybody have any input on this at all?
>
> On Thursday, April 10, 2014 11:59:43 AM UTC+12, Tim Uckun wrote:
>
> > I want to do something like this.
> >
> > select date\_trunc('month', time\_stamp), sum(distinct\_count) from (
> > select date\_trunc('week', time\_stamp) as time\_stamp, count(distinct
> > field\_name) as distinct\_count
> > from blah
> > group by date\_trun('week', time\_stamp)
> > )
> > group by date\_trunc('month', time\_stamp)
> >
> > So basically I want to break up the data into weekly chunks and count the
> > distinct appearances of a value and then sum those up on a per monthly
> > basis.
> >
> > In preparation for that I tried to do the subquery for that which looks
> > like this
> >
> > aggregations: {
> > by\_month: {
> > date\_histogram: {
> > field: "time\_stamp",
> > interval: "1M",
> > format: "yyyy-MM-dd HH:mm"
> > },
> > aggregations: {
> > by\_node\_mac: {
> > terms: {
> > field: "node\_mac"
> > },
> > aggregations: {
> > cardinality: {field: 'device\_mac'}
> > }
> >
> > ```
> > }
> > }
> > }
> > }
> >
> > ```
> >
> > }
> >
> > but I seem to be getting the wrong answers. I am using fake data which
> > should give me very low numbers for the cardinality but it actually seems
> > to be counting the number of rows not the number of distinct items. The
> > numbers are outrageously high.
> >
> > I tried a precision threshold of 1000 and 100 but it seems to make no
> > difference.
> >
> > --
> > 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/016b94f6-4b62-4009-83ec-ccef3bfb17f6%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/016b94f6-4b62-4009-83ec-ccef3bfb17f6%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/016b94f6-4b62-4009-83ec-ccef3bfb17f6%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/016b94f6-4b62-4009-83ec-ccef3bfb17f6%40googlegroups.com?utm_medium=email&utm_source=footer)
> > .
>
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
--
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/CAGCwEM8JwkQHOsxUwt6ddH2W-f1nNcttUvGWq0AXZiFmVt9bSQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAGCwEM8JwkQHOsxUwt6ddH2W-f1nNcttUvGWq0AXZiFmVt9bSQ%40mail.gmail.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 1:34am UTC](https://discuss.elastic.co/t/sbuqueries-and-distinct-counts/16915/4 "2017-07-06T01:34:40Z")