Multiple columns aggregates

InfluxDB 2
,
1

Hello,
I get a table “_RESULT” with a final Union step, all is working well.
I got the result by day (based on my calculation needed by day).
I would like to sum by month all my 3 “Total” Columns.

I really do not know how to do it.
Thanks in advance for your support

Union Table result

image
image1758×377 35.5 KB

Just for you information, step of the union (all works well)

// Cout Tarif Jour
COST_Heure = from(bucket: "Jeedom") 
  |> range(start: -100y)
  |> filter(fn: (r) => r["_measurement"] == "Cout_Heure_Creuse" or r["_measurement"] == "Cout_Heure_Pleine" or r["_measurement"] == "Cout_Abonnement")
  |> filter(fn: (r) => r["_field"] == "Prix_kw")
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
  |> keep(columns: ["_time","Cout_Heure_Creuse","Cout_Heure_Pleine"])
//  |> keep(columns: ["_time","Cout_Heure_Creuse","Cout_Heure_Pleine","Cout_Abonnement"])
  |> truncateTimeColumn(unit: 1d)

// Conso par Jour
CONSO = from(bucket: "Jeedom")
  |> range(start: -100y)
  |> filter(fn: (r) => r["_measurement"] == "STAT_TODAY" or r["_measurement"] == "STAT_TODAY_HC" or r["_measurement"] == "STAT_TODAY_HP")
  |> filter(fn: (r) => r["_field"] == "Compteur")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
  |> keep(columns: ["_time","STAT_TODAY","STAT_TODAY_HC","STAT_TODAY_HP"])
  // On va décaller d'une seconde en arrière pour retrouver la journée de consommation sinon attribué à la journée suivante car 00:00
  |> timeShift(duration: -1s, columns: ["_time"])
  |> truncateTimeColumn(unit: 1d)
  
//Merge des 2 tables
union(tables: [COST_Heure, CONSO])
2

After saving unionTables = union(tables: [COST_Heure, CONSO]) (dunno if needed)
You can:

|> map(
        fn: (r) => ({r with
            Total: r.Total_Cout_Heure + r.Total_Cout_Heure_Creuse + r.Total_Cout_Heure_Pleine
        }),
    ) 
  |> aggregateWindow(every: 1mo, fn: sum, column: "Total")

Total can be exchanged for your naming.

look at aggregateWindow() if additional parameters need to be set

3

Thank you very much,

If I want to do the sum for all Totals, Total_cout_Heure, Total_Cout_Heure_creuse, do I need to do multiple aggregatewindow and create a join by _time ?

4

The map() function sums al your Totals. If you want to add more you can do that. After the map() function you should have a “Total” value column for all your Totals. And you aggregate them (sum) together for each month.
It should work with the function i gave you (or should be close)

And if i missunderstood and you want each Total column and them summed by month. I think you need as many aggregateWindows and no map.

Maybe you can use the aggregateWindow with a custom function, but i don´t have enough experience to know that. maybe this link helps

so yes:

|> aggregateWindow(every: 1mo, fn: sum, column: “Totals”)
|> aggregateWindow(every: 1mo, fn: sum, column: “Total_cout_Heure”)
|> aggregateWindow(every: 1mo, fn: sum, column: “Total_Cout_Heure_creuse”)

and join them by _time.
You can only join 2 together so you need to join the first two and then the other one. Also you can rename() the columns.

5

Thanks a lot.
I have updated all my query with your inputs.
All is working well now