Merging multiple rows into single row with % contribution

user_1719825 0
Viewed 443

I have a table with node ID, operation name and number of failure of given type. I'd like to collapse nodes with same ID by adding a new column that gives relative % of operations failures. Specifically here is what the table looks like

let nodeFailures = datatable(nodeId:string, OpName:string, failureCount:long)
[
    "A", "DemoOp1", 2000, 
    "A", "DemoOp2", 4000, 
    "A", "DemoOp3", 4000, 
    "B", "DemoOp1", 4500, 
    "B", "DemoOp4", 4500 
];

I'd like to collapse it to 2 rows - Something like

nodeFailuresSummarized = datatable(nodeId:string, failureCount:long, analysis: string)
[
    "A", 10000, "DemoOp1(20%), DemoOp2(40%), DemoOp3(40%)",
    "B", 9000, "DemoOp1(50%), DemoOp4(50%)"
];

I've been playing games with summarize and percentile*() aggregation without much luck.. Will appreciate help here.. Thanks so much!

kql azure-data-explorer azure
1 Answers
let nodeFailures = datatable(nodeId:string, OpName:string, failureCount:long)
[
    "A", "DemoOp1", 2000, 
    "A", "DemoOp2", 4000, 
    "A", "DemoOp3", 4000, 
    "B", "DemoOp1", 4500, 
    "B", "DemoOp4", 4500 
];
let totalFailures = toscalar(nodeFailures | summarize sum(failureCount));
nodeFailures
| extend Percent = round(failureCount * 100.0 / totalFailures, 2)
| summarize sum(failureCount), List = make_list(strcat(OpName, " (", Percent, "%)")) by nodeId
| extend List = strcat_array(List, ", ")

The output will be:

| nodeId  | sum_failureCount | List|
| A       | 10000            | DemoOp1 (10.53%), DemoOp2 (21.05%), DemoOp3 (21.05%)|
| B       | 9000             | DemoOp1 (23.68%), DemoOp4 (23.68%)             
        |
user_21450970
Related