Search code examples
azureazure-data-explorerkql

Adding a last row using KQL to show grand total based on column values

I have data in following format

let T = datatable(OperationName:string, Result:string)
[
   "Method1" , "success",
   "Method1" , "failure",
   "Method1" , "success",
   "Method1" , "success",
   "Method1" , "success",
   "Method1" , "failure",
   "Method2" , "success",
   "Method2" , "failure",
   "Method2" , "failure",
];
T 
| summarize success = countif(Result == "success")
           ,failure = countif(Result == "failure")
           ,total   = count()
            by OperationName

Which displays the number of successes and failures per method along with the total.

enter image description here

Is it possible to calculate a grand total value, which is the sum of the total column values, and display it at the end of the last row, as shown below?

enter image description here

Solution

  • you could try unioning with a row that is an aggregation of the aggregated table.

    for example:

    let T = datatable(OperationName: string, Result: string)
[
    "Method1", "success",
    "Method1", "failure",
    "Method1", "success",
    "Method1", "success",
    "Method1", "success",
    "Method1", "failure",
    "Method2", "success",
    "Method2", "failure",
    "Method2", "failure",
];
T 
| summarize
    success = countif(Result == "success"),
    failure = countif(Result == "failure"),
    total   = count()
 by OperationName
| as hint.materialized=true T
| union (T | summarize success = sum(success), failure = sum(failure), total = sum(total) by OperationName = "Grand Total")
    OperationName success failure total
    Method2 1 2 3
    Method1 4 2 6
    Grand Total 5 4 9

    or, if you're interested in a less-intuitive and less-structured output (for reasons of your own):

    let T = datatable(OperationName: string, Result: string)
[
    "Method1", "success",
    "Method1", "failure",
    "Method1", "success",
    "Method1", "success",
    "Method1", "success",
    "Method1", "failure",
    "Method2", "success",
    "Method2", "failure",
    "Method2", "failure",
];
T 
| summarize
    success = countif(Result == "success"),
    failure = countif(Result == "failure"),
    total   = count()
 by OperationName
| as hint.materialized=true T
| union (T | summarize total = sum(total) by OperationName = "")
| extend total = case(isempty(OperationName), strcat("Grand Total = ", total), tostring(total))
    OperationName success failure total
    Method2 1 2 3
    Method1 4 2 6
    Grand Total = 9