# Difference between counts

**URL:** <https://community.influxdata.com/t/difference-between-counts/32449>\
**Category:** Systems\
**Created:** [December 9, 2023, 3:28pm UTC](https://community.influxdata.com/t/difference-between-counts/32449 "2023-12-09T15:28:52Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdang](https://avatars.discourse-cdn.com/v4/letter/m/c37758/32.png) [@mdang](https://community.influxdata.com/u/mdang)\
**Post date:** [December 9, 2023, 3:28pm UTC](https://community.influxdata.com/t/difference-between-counts/32449/1 "2023-12-09T15:28:52Z")

</div>

Hello,

I tried to use difference() to find the offset between current & previous counts but seems not working properly.

```auto
from(bucket: "Dialout")
  |> range(start: -30s, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "show interface status")
  |> filter(fn: (r) => r["source"] == "device")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["_value"] == "connected")
  |> last()  
  |> unique(column: "TABLE_interface")  
  |> group(columns: ["host", "_measurement"], mode:"by")  
  |> count()
  |> difference(nonNegative: false, columns: ["_value"])

```

Can someone give me a hint?

Regards,  
Minh

---

<div class="post-metadata">

**Author:** ![grant1](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/grant1/32/15107_2.png) [@grant1](https://community.influxdata.com/u/grant1)\
**Post date:** [December 9, 2023, 9:58pm UTC](https://community.influxdata.com/t/difference-between-counts/32449/2 "2023-12-09T21:58:42Z")

</div>

Welcome @mdang

Can you clarify what you mean by “previous count”? The count of your value 30 seconds ago?

I presume that “current count” means the count of the most recent values in the database?

---

<div class="post-metadata">

**Author:** ![mdang](https://avatars.discourse-cdn.com/v4/letter/m/c37758/32.png) [@mdang](https://community.influxdata.com/u/mdang)\
**Post date:** [December 10, 2023, 1:01am UTC](https://community.influxdata.com/t/difference-between-counts/32449/3 "2023-12-10T01:01:52Z")

</div>

Thanks for looking into this.

Correct. My goal is to get the diff between the most recent count vs 30s ago in the database.

---

<div class="post-metadata">

**Author:** ![grant1](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/grant1/32/15107_2.png) [@grant1](https://community.influxdata.com/u/grant1)\
**Post date:** [December 10, 2023, 3:56am UTC](https://community.influxdata.com/t/difference-between-counts/32449/4 "2023-12-10T03:56:09Z")

</div>

I think a union will be needed. Something like this…

```auto
FirstPeriod1 = from(bucket: "Dialout")
  |> range(start: -31s, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "show interface status")
  |> filter(fn: (r) => r["source"] == "device")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["_value"] == "connected")
  |> last()  
  |> unique(column: "TABLE_interface")  
  |> group(columns: ["host", "_measurement"], mode:"by")  
  |> count()
  |> set(key: "newValue", value: "first_value_of_period1")
  |> yield(name: "first_period1")

 LastPeriod1 = from(bucket: "Dialout")
  |> range(start: -60s, stop: -30s)
  |> filter(fn: (r) => r["_measurement"] == "show interface status")
  |> filter(fn: (r) => r["source"] == "device")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["_value"] == "connected")
  |> last()  
  |> unique(column: "TABLE_interface")  
  |> group(columns: ["host", "_measurement"], mode:"by")  
  |> count()
  |> set(key: "newValue", value: "last_value_of__period1")
  |> yield(name: "last_period1")

Period1_Union = union(tables: [FirstPeriod1, LastPeriod1])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with period1_difference: r.last_value_of_period1 - r.first_value_of_period1 }))    
  |> yield(name: "Period1_Union")

```