Kusto summarize unique occurrences of the value in the column

1
votes

I have following dataset:

let t1 = datatable(id:string, col1:string, col2:string)
[
    '1', 'ValueA', 'AT',
    '2', 'ValueC', 'AT',
    '3', 'ValueA', 'AT',
    '4', 'ValueB', 'AT',
    '1', 'ValueC', 'v-username',
];
t1 
| summarize (Id) by col1

My goal is to count occurrences of values in col1 per Id. Because ID=1 occurs twice, I need to decide whether to take ValueA or ValueC. This is decided by value of col2. If col2 startswith "v-" then take Value from this row. When I use "summarize (Id) by col1" I am getting:

ValueA,2
ValueC,2
ValueB,1
ValueD,1
Total:6

Expected result is:

ValueA,1
ValueC,2
ValueB,1
ValueD,1
Total:5

Is it possible to achieve with Kusto?

1
azure-data-explorersummarize

1 Answers

1
votes

a. when you run ... | summarize (id) by col1" you should get a semantic error as there's no aggregation function specified (e.g. you could have run... | summarize dcount(id) by col1`)

b. it's not clear where ValueD, 1, in your expected result, come from. as your datatable expression includes no record with ValueD

c. if i had to guess the solution to your question, despite a and b, this would be my guess:

let t1 = datatable(id:string, col1:string, col2:string)
[
    '1', 'ValueA', 'AT',
    '2', 'ValueC', 'AT',
    '3', 'ValueD', 'AT',
    '4', 'ValueB', 'AT',
    '1', 'ValueC', 'v-username',
];
t1 
| summarize c = dcount(id) by col1
| as T
| union (T | summarize c = sum(c) by col1 = "Total")