# Inconsistent aggregation results when grouping by time

**URL:** <https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206>\
**Category:** Systems\
**Created:** [March 13, 2017, 4:34pm UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206 "2017-03-13T16:34:59Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![shermster](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@shermster](https://community.influxdata.com/u/shermster)\
**Post date:** [March 13, 2017, 4:34pm UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/1 "2017-03-13T16:34:59Z")

</div>

Hi,

I’m trying to group a set of call data records per hour. The sample dataset is available here

[https://dl.dropboxusercontent.com/u/21030364/call\_details\_influxdb.zip](https://dl.dropboxusercontent.com/u/21030364/call_details_influxdb.zip)

and can be unzipped and imported using

```auto
influx -import -path=call_details_influxdb_filtered -precision=s

```

When I run the following sample query

```auto
select count(asterisk_uniqueid) as call_count, mean(talk_time) as talk_time_average, sum(talk_time) as talk_time_total, min(talk_time) as talk_time_min, max(talk_time) as talk_time_max, mean(waiting_time) as waiting_time_average, sum(waiting_time) as waiting_time_total, min(waiting_time) as waiting_time_min, max(waiting_time) as waiting_time_max, mean(original_position) as original_position_average, min(original_position) as original_position_min, max(original_position) as original_position_max from call_details where time >= '2017-02-26' AND time <= '2017-03-10' AND queue='ob-7018' GROUP BY queue,call_disposition,time(1d) fill(0) order by time desc

```

I get a result set with two results - which is correct.

If I narrow the date range and run the following

```auto
select count(asterisk_uniqueid) as call_count, mean(talk_time) as talk_time_average, sum(talk_time) as talk_time_total, min(talk_time) as talk_time_min, max(talk_time) as talk_time_max, mean(waiting_time) as waiting_time_average, sum(waiting_time) as waiting_time_total, min(waiting_time) as waiting_time_min, max(waiting_time) as waiting_time_max, mean(original_position) as original_position_average, min(original_position) as original_position_min, max(original_position) as original_position_max from call_details where time >= '2017-02-26' AND time <= '2017-03-02' AND queue='ob-7018' GROUP BY queue,call_disposition,time(1d) fill(0) order by time desc

```

I get three result sets. The set with tags: call\_disposition=ANSWERED, queue=ob-7018 appears twice.

I really don’t understand why this is happening. Why aren’t they merging in the second scenario. Can someone please explain what is possibly going wrong?

Thanks,  
Ian

---

<div class="post-metadata">

**Author:** ![michael](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael/32/2902_2.png) [@michael](https://community.influxdata.com/u/michael)\
**Post date:** [March 14, 2017, 2:41pm UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/2 "2017-03-14T14:41:33Z")

</div>

Can you include the output you’re getting.

---

<div class="post-metadata">

**Author:** ![shermster](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@shermster](https://community.influxdata.com/u/shermster)\
**Post date:** [March 15, 2017, 6:53am UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/3 "2017-03-15T06:53:08Z")

</div>

This is a simplified query of the above on the same dataset that demonstrates the issue I’m having

When querying from the 26 Feb to 10 March I get the correct results. One group for ANSWERED and one group for UNANSWERED (these are the two distinct call\_dispositions)

```auto
select count(asterisk_uniqueid) as call_count from call_details where time >= '2017-02-26' AND time <= '2017-03-10' AND queue='ob-7018' GROUP BY call_disposition,time(1d) fill(0) order by time desc
name: call_details
tags: call_disposition=UNANSWERED
time call_count
---- ----------
2017-03-10T00:00:00Z 0
2017-03-09T00:00:00Z 1
2017-03-08T00:00:00Z 3
2017-03-07T00:00:00Z 1
2017-03-06T00:00:00Z 2
2017-03-05T00:00:00Z 3
2017-03-04T00:00:00Z 2
2017-03-03T00:00:00Z 0
2017-03-02T00:00:00Z 0
2017-03-01T00:00:00Z 5
2017-02-28T00:00:00Z 0
2017-02-27T00:00:00Z 0
2017-02-26T00:00:00Z 0

name: call_details
tags: call_disposition=ANSWERED
time call_count
---- ----------
2017-03-10T00:00:00Z 0
2017-03-09T00:00:00Z 9
2017-03-08T00:00:00Z 17
2017-03-07T00:00:00Z 16
2017-03-06T00:00:00Z 25
2017-03-05T00:00:00Z 41
2017-03-04T00:00:00Z 19
2017-03-03T00:00:00Z 0
2017-03-02T00:00:00Z 9
2017-03-01T00:00:00Z 31
2017-02-28T00:00:00Z 6
2017-02-27T00:00:00Z 0
2017-02-26T00:00:00Z 0

```

When querying from the 26 Feb to 2 March I get the **incorrect** results. There are 3 groups with the ANSWERED group repeated

```auto
> select count(asterisk_uniqueid) as call_count from call_details where time >= '2017-02-26' AND time <= '2017-03-02' AND queue='ob-7018' GROUP BY call_disposition,time(1d) fill(0) order by time desc
name: call_details
tags: call_disposition=ANSWERED
time call_count
---- ----------
2017-03-02T00:00:00Z 0
2017-03-01T00:00:00Z 31
2017-02-28T00:00:00Z 0
2017-02-27T00:00:00Z 0
2017-02-26T00:00:00Z 0

name: call_details
tags: call_disposition=UNANSWERED
time call_count
---- ----------
2017-03-02T00:00:00Z 0
2017-03-01T00:00:00Z 5
2017-02-28T00:00:00Z 0
2017-02-27T00:00:00Z 0
2017-02-26T00:00:00Z 0

name: call_details
tags: call_disposition=ANSWERED
time call_count
---- ----------
2017-03-02T00:00:00Z 0
2017-03-01T00:00:00Z 0
2017-02-28T00:00:00Z 6
2017-02-27T00:00:00Z 0
2017-02-26T00:00:00Z 0

```

Why is the ANSWERED group merged in the first example and not in the second?

---

<div class="post-metadata">

**Author:** ![michael](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael/32/2902_2.png) [@michael](https://community.influxdata.com/u/michael)\
**Post date:** [March 15, 2017, 7:04am UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/4 "2017-03-15T07:04:41Z")

</div>

Very weird. A few more asks.

1. What version of InfluxDB are you using?

2. Can you change the time format so that the results are specified in seconds instead of rfc3339 and rerun the query? I wan’t to write the same data into my local instance to test it out.

3. What are the results of `SHOW SERIES`, `SHOW TAG KEYS`, and `SHOW MEASUREMENTS`?

---

<div class="post-metadata">

**Author:** ![shermster](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@shermster](https://community.influxdata.com/u/shermster)\
**Post date:** [March 15, 2017, 7:19am UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/5 "2017-03-15T07:19:03Z")

</div>

Version 1.2.0

The original data is available in that Dropbox link if you want to import into your own instance.

```auto
> select count(asterisk_uniqueid) as call_count from call_details where time >= '2017-02-26' AND time <= '2017-03-02' AND queue='ob-7018' GROUP BY call_disposition,time(1d) fill(0) order by time desc
name: call_details
tags: call_disposition=ANSWERED
time call_count
---- ----------
1488412800 0
1488326400 31
1488240000 0
1488153600 0
1488067200 0

name: call_details
tags: call_disposition=UNANSWERED
time call_count
---- ----------
1488412800 0
1488326400 5
1488240000 0
1488153600 0
1488067200 0

name: call_details
tags: call_disposition=ANSWERED
time call_count
---- ----------
1488412800 0
1488326400 0
1488240000 6
1488153600 0
1488067200 0

```

```auto
> SHOW TAG KEYS
name: call_details
tagKey
------
agent
call_direction
call_disposition
global_server_id
queue
time_zone

```

```auto
> SHOW MEASUREMENTS
name: measurements
name
----
call_details

```

Show series exceeds the post limit on this forum. I’ve put the results here

[http://pastebin.com/raw/VPfcxiQP](http://pastebin.com/raw/VPfcxiQP)

---

<div class="post-metadata">

**Author:** ![michael](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael/32/2902_2.png) [@michael](https://community.influxdata.com/u/michael)\
**Post date:** [March 15, 2017, 8:12am UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/6 "2017-03-15T08:12:10Z")

</div>

This looks like a bug. Can you please open an [issue](https://github.com/influxdata/influxdb/issues/new) on the InfluxDB repo.

---

<div class="post-metadata">

**Author:** ![michael](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael/32/2902_2.png) [@michael](https://community.influxdata.com/u/michael)\
**Post date:** [March 15, 2017, 8:20am UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/7 "2017-03-15T08:20:29Z")

</div>

Note that without the `ORDER BY time DESC` it works correctly.

```auto
> select count(asterisk_uniqueid) as call_count from call_details where time >= '2017-02-26' AND time <= '2017-03-02' AND queue='ob-7018' GROUP BY call_disposition,time(1d) fill(0)
name: call_details
tags: call_disposition=ANSWERED
time call_count
---- ----------
1488067200000000000	0
1488153600000000000	0
1488240000000000000	6
1488326400000000000	31
1488412800000000000	0

name: call_details
tags: call_disposition=UNANSWERED
time call_count
---- ----------
1488067200000000000	0
1488153600000000000	0
1488240000000000000	0
1488326400000000000	5
1488412800000000000	0

```

---

<div class="post-metadata">

**Author:** ![michael](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/michael/32/2902_2.png) [@michael](https://community.influxdata.com/u/michael)\
**Post date:** [March 15, 2017, 8:21am UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/8 "2017-03-15T08:21:28Z")

</div>

@shermster Sorry for having you run all of those queries before. I missed that you included the dataset in your original post.

---

<div class="post-metadata">

**Author:** ![shermster](https://avatars.discourse-cdn.com/v4/letter/s/f17d59/32.png) [@shermster](https://community.influxdata.com/u/shermster)\
**Post date:** [March 15, 2017, 8:25am UTC](https://community.influxdata.com/t/inconsistent-aggregation-results-when-grouping-by-time/206/9 "2017-03-15T08:25:28Z")

</div>

Thanks Michael for investigating this. I can manage then without the time sorting.

I did open a bug about this yesterday so I will update it with a link to this discussion.

> <https://github.com/influxdata/influxdb/issues/8130>
>
> \### Bug report
> 
> \_\_System info:\_\_ \[InfluxDB 1.2.0 running on Centos 6\]
> 
> \_\_Ste…ps to reproduce:\_\_
> 
> There may be a simple explanation for this but I'm just struggling to understand the results I'm getting for aggregations grouped by a time period.
> 
> 1. The data set being used is available here https://dl.dropboxusercontent.com/u/21030364/call\_details\_influxdb.zip and can be loaded using
> 
> influx -import -path=call\_details\_influxdb\_filtered -precision=s
> 
> There are 10,000 records of timestamped call data records.
> 
> 2. I'm getting inconsistent grouping results depending on the time period specified in the where clause.
> 
> For example running the query from the 26 Feb to 10 March
> 
> select count(asterisk\_uniqueid) as call\_count from call\_details where time \>= '2017-02-26' AND time \<= '2017-03-10' AND queue='ob-7018' GROUP BY call\_disposition,time(1d) fill(0) order by time desc
> 
> This produces the correct result set with results for call\_disposition ANSWERED and call\_disposition UNANSWERED
> 
> \`\`\`
> name: call\_details
> tags: call\_disposition=UNANSWERED
> time call\_count
> 2017-03-10T00:00:00Z 0
> 2017-03-09T00:00:00Z 1
> 2017-03-08T00:00:00Z 3
> 2017-03-07T00:00:00Z 1
> 2017-03-06T00:00:00Z 2
> 2017-03-05T00:00:00Z 3
> 2017-03-04T00:00:00Z 2
> 2017-03-03T00:00:00Z 0
> 2017-03-02T00:00:00Z 0
> 2017-03-01T00:00:00Z 5
> 2017-02-28T00:00:00Z 0
> 2017-02-27T00:00:00Z 0
> 2017-02-26T00:00:00Z 0
> 
> 
> name: call\_details
> tags: call\_disposition=ANSWERED
> time call\_count
> 2017-03-10T00:00:00Z 0
> 2017-03-09T00:00:00Z 9
> 2017-03-08T00:00:00Z 17
> 2017-03-07T00:00:00Z 16
> 2017-03-06T00:00:00Z 25
> 2017-03-05T00:00:00Z 41
> 2017-03-04T00:00:00Z 19
> 2017-03-03T00:00:00Z 0
> 2017-03-02T00:00:00Z 9
> 2017-03-01T00:00:00Z 31
> 2017-02-28T00:00:00Z 6
> 2017-02-27T00:00:00Z 0
> 2017-02-26T00:00:00Z 0
> \`\`\`
> 
> However if I shorten the time period for example 26 Feb to 2 March
> 
> select count(asterisk\_uniqueid) as call\_count from call\_details where time \>= '2017-02-26' AND time \<= '2017-03-02' AND queue='ob-7018' GROUP BY call\_disposition,time(1d) fill(0) order by time desc
> 
> I then get three results 
> 
> \`\`\`
> name: call\_details
> \*\*tags: call\_disposition=ANSWERED\*\*
> time call\_count
> 2017-03-02T00:00:00Z 0
> 2017-03-01T00:00:00Z 31
> 2017-02-28T00:00:00Z 0
> 2017-02-27T00:00:00Z 0
> 2017-02-26T00:00:00Z 0
> 
> name: call\_details
> tags: call\_disposition=UNANSWERED
> time call\_count
> 2017-03-02T00:00:00Z 0
> 2017-03-01T00:00:00Z 5
> 2017-02-28T00:00:00Z 0
> 2017-02-27T00:00:00Z 0
> 2017-02-26T00:00:00Z 0
> 
> name: call\_details
> \*\*tags: call\_disposition=ANSWERED\*\*
> time call\_count
> 2017-03-02T00:00:00Z 0
> 2017-03-01T00:00:00Z 0
> 2017-02-28T00:00:00Z 6
> 2017-02-27T00:00:00Z 0
> 2017-02-26T00:00:00Z 0
> \`\`\`
> 
> Why is this happening? I don't understand why there are two ANSWERED groups?
> 
> Regards,
> Ian

Appreciate the quick help.