CODEWITHSUNDEEP
azure-data-explorerkql
Noobie2021
Noobie2021

Reputation: 371

Sum values of a column based on a condition from another column in kusto?

I have the following dataset:

let data = datatable(Timestamp:datetime, Name:string, Status:string, Energy: real)
            [
             datetime(2022-02-18 10:00:00 AM), "Device_1", "uptime", 1.0,
             datetime(2022-02-18 10:01:00 AM), "Device_2", "downtime", 2.0,
             datetime(2022-02-18 10:02:00 AM), "Device_3", "uptime", 3.0,
             datetime(2022-02-18 10:03:00 AM), "Device_4", "downtime", 4.0, 
             datetime(2022-02-18 10:04:00 AM), "Device_5", "uptime", 5.0,
             datetime(2022-02-18 10:05:00 AM), "Device_6", "downtime", 6.0,
             datetime(2022-02-18 10:06:00 AM), "Device_7", "uptime", 7.0,
             datetime(2022-02-18 10:07:00 AM), "Device_8", "downtime", 8.0,
             datetime(2022-02-18 10:08:00 AM), "Device_9", "uptime", 9.0,
             datetime(2022-02-18 10:09:00 AM), "Device_10", "downtime", 10.0
            ];
data
| extend EnergyUptime = iff(Status == "uptime", toreal(Energy), toreal(0))

What I am trying to achieve here is to sum up the EnergyUptime if the "Device_1" and "Device_3" and "Device_5" where existing in the dataset. If they are not existing then the sum should be 0

The expected result here should be = 1 + 3 + 5 + 7 + 9 = 25

I tried to use summarize sum(iff(Name has "Device_1" or "Device_3" or "Device_5"), EnergyUptime, toreal(0))) but in this case it only sums up the values for these devices. It should also consider Device_7 and Device_9

Can someone please provide me with a solution to this problem ?

Upvotes: 1

Views: 1373

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

let data = datatable(Timestamp:datetime, Name:string, Status:string, Energy: real)
            [
             datetime(2022-02-18 10:00:00 AM), "Device_1", "uptime", 1.0,
             datetime(2022-02-18 10:01:00 AM), "Device_2", "downtime", 2.0,
             datetime(2022-02-18 10:02:00 AM), "Device_3", "uptime", 3.0,
             datetime(2022-02-18 10:03:00 AM), "Device_4", "downtime", 4.0, 
             datetime(2022-02-18 10:04:00 AM), "Device_5", "uptime", 5.0,
             datetime(2022-02-18 10:05:00 AM), "Device_6", "downtime", 6.0,
             datetime(2022-02-18 10:06:00 AM), "Device_7", "uptime", 7.0,
             datetime(2022-02-18 10:07:00 AM), "Device_8", "downtime", 8.0,
             datetime(2022-02-18 10:08:00 AM), "Device_9", "uptime", 9.0,
             datetime(2022-02-18 10:09:00 AM), "Device_10", "downtime", 10.0
            ];
let RequestedDevices = dynamic(["Device_1", "Device_3", "Device_5"]);            
data
| summarize EnergyUptime = sumif(Energy, Status == "uptime"), Devices = make_set_if(Name, Name in (RequestedDevices))
| project EnergyUptime = iff(Devices has_all (RequestedDevices), EnergyUptime, real(0))
EnergyUptime
25

Fiddle

Upvotes: 1

Related Questions