# How to convert influxql query into flux

**URL:** <https://community.influxdata.com/t/how-to-convert-influxql-query-into-flux/23217>\
**Category:** Fluxlang\
**Tags:** influxql, flux\
**Created:** [January 4, 2022, 11:10am UTC](https://community.influxdata.com/t/how-to-convert-influxql-query-into-flux/23217 "2022-01-04T11:10:57Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Moacir\_Ferreira](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/moacir_ferreira/32/9127_2.png) [@Moacir\_Ferreira](https://community.influxdata.com/u/Moacir_Ferreira)\
**Post date:** [January 4, 2022, 11:10am UTC](https://community.influxdata.com/t/how-to-convert-influxql-query-into-flux/23217/1 "2022-01-04T11:10:57Z")

</div>

I am trying to migrate from InlfuxDb 1 to 2. To control some of my processes I have the following query in influxql where the goal is to get the total power consumption to date:

SELECT last(“value”) - first(“value”) FROM “PowerData”.“autogen”.“kWh” WHERE time \> ‘2022-01-01T00:00:00Z’ AND time \< ‘2022-01-01T23:59:59Z’ AND “entity\_id”=‘kWhPower’

So far I got in flux:

data = from(bucket: “PowerData”)  
|\> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)  
|\> filter(fn: (r) =\> r[“topic”] == “Kaifa”)  
|\> filter(fn: (r) =\> r["\_field"] == “Kaifa\_kWhPower”)

last = data  
|\> last()  
|\> set(key: “\_field”, value: “Power”)

first = data  
|\> first()  
|\> set(key: “\_field”, value: “Power”)

union(tables: [last, first])  
|\> difference()

But how can I select the last and first value in range, subtract them and get a single number as the response? Am I doing this on its best way or there are better ways to do it?

Thanks for helping!

---

<div class="post-metadata">

**Author:** ![scott](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/scott/32/16492_2.png) [@scott](https://community.influxdata.com/u/scott)\
**Post date:** [January 5, 2022, 6:19pm UTC](https://community.influxdata.com/t/how-to-convert-influxql-query-into-flux/23217/2 "2022-01-05T18:19:26Z")

</div>

@Moacir_Ferreira There are a few different ways to do this.

If your data is only incrementing up, you can use [`spread()`](https://docs.influxdata.com/flux/v0.x/stdlib/universe/spread/) to return the difference between the minimum and maximum values in each table. So if the first value is always the lowest and the last value is always the highest, `spread()` is the way to go:

```javascript
from(bucket: "PowerData")
    |> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)
    |> filter(fn: (r) => r["topic"] == "Kaifa")
    |> filter(fn: (r) => r["_field"] == "Kaifa_kWhPower")
    |> spread()

```

Something else to note here is that if your kWh value every resets (like some counters do), you can use [`increase()`](https://docs.influxdata.com/flux/v0.x/stdlib/universe/increase/) before `spread()` to normalize the values after the reset.

If values fluctuate up and down and you only want to return the difference between the first and last values, the query you have, as far as performance and optimization, is great. The only thing I would add is to sort by time after the union to ensure the rows are in the correct order:

```javascript
data = from(bucket: "PowerData")
    |> range(start: 2022-01-01T00:00:00Z, stop: 2022-01-31T23:59:59Z)
    |> filter(fn: (r) => r["topic"] == "Kaifa")
    |> filter(fn: (r) => r["_field"] == "Kaifa_kWhPower")

last = data |> last() |> set(key: "_field", value: "Power")
first = data |> first() |> set(key: "_field", value: "Power")

union(tables: [last, first]) |> sort(columns: ["_time"], desc: true) |> difference()

```

Another option would be to create a custom aggregate using `reduce()`, but it won’t be as efficient as either of the queries above.

---

<div class="post-metadata">

**Author:** ![Moacir\_Ferreira](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/moacir_ferreira/32/9127_2.png) [@Moacir\_Ferreira](https://community.influxdata.com/u/Moacir_Ferreira)\
**Post date:** [January 5, 2022, 7:50pm UTC](https://community.influxdata.com/t/how-to-convert-influxql-query-into-flux/23217/3 "2022-01-05T19:50:48Z")

</div>

Many thanks Scott! My case is the first one. It works perfectly!