Question 265968 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – KQL – Get Sum of values of Multiple Columns

I get the following data from a query:

Service 201 202 401  // 402 etc
A       100 50  20   
C       25  0   0

The columns are dynamic. It sometimes there can be just 201, sometimes 200, 201, 202, 204, etc

I want to get the following result:

Service 201 202 503  2xxCount  5xxCount
A       100 50  20   150       20        
C       25  0   0    25        0

As I said, the columns are dynamic. i want to calculate sum of all columns whose name starts with 2, as 2xxCount and 5 as 5xxCount.

Tags:

2

Answers


  1. 0

    You can achieve this in 2 steps – first generate the query dynamically, and then execute it:

    1. Generate query:
    let T = datatable (Service:string, ['201']:long, ['202']:long, ['401']:long)
[
    "A", 100, 50, 20, 
    "C", 25, 0, 0
];
T | getschema 
| where ColumnName != "Service"
| extend Column = bin(tolong(ColumnName), 100)
| summarize make_list(strcat("['", ColumnName, "']")) by Column
| project strcat("['", Column/100, "xxCount'] = ", strcat_array(list_, " + "))
| summarize ExtendedColumns = strcat_array(make_list(Column1), ", ")
    ExtendedColumns
    [‘2xxCount’] = [‘201’] + [‘202’], [‘4xxCount’] = [‘401’]
    1. Run it:
    let T = datatable (Service:string, ['201']:long, ['202']:long, ['401']:long)
[
    "A", 100, 50, 20, 
    "C", 25, 0, 0
];
T
| extend ['2xxCount'] = ['201'] + ['202'], ['4xxCount'] = ['401']
    Service 201 202 401 2xxCount 4xxCount
    A 100 50 20 150 20
    C 25 0 0 25 0
    Login or Signup to reply.
  2. 0

    You should be able to achieve this by a combined use of narrow and pivot:

    let T = datatable (Service:string, ['201']:long, ['202']:long, ['401']:long)
[
    "A", 100, 50, 20, 
    "C", 25, 0, 0
];
let categorizedData=T
| evaluate narrow()
| parse Column with "['" Code "']"
| extend Column = coalesce(Code, Column)   // strip the [' '] from the column names
| extend CodeCategory = strcat(toint(Code)/100, 'xx');
let sumByCategory=categorizedData
| where isnotempty(Code)
| summarize Value=tostring(sum(toint(Value))) by Column=CodeCategory, Row;
categorizedData
| union sumByCategory
| project-away Code, CodeCategory
| evaluate pivot(Column, take_any(Value))
| project-away Row

    Result:

    201 202 2xx 401 4xx Service
    25 0 25 0 0 C
    100 50 150 20 20 A

    Only slightly annoying bit is that you have no control over the column order returned by pivot but if the max number of columns is fixed you can hard-code a re-order if it’s important to you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search