2
votes

I'm new to Kusto and I'm trying to do grouping using summarize where I can specify additional columns to display for the value on which I'm grouping.

This is what I'm trying to do, mentioned in standard SQL:

select UserId, LocationId, COUNT(*) as ErrorCount from SampleTable where ResultType != 'Success'
group by UserId
order by ErrorCount desc

I'm grouping by UserId, but then I'm displaying the LocationId for that UserId too in the grouping result

Converting the above to Kusto, I am writing this:

SampleTable
| where ResultType != "Success"
| summarize ErrorCount=count() by UserId
| project UserId, LocationId, ErrorCount
| sort by ErrorCount desc

But it doesn't work. Kusto complains that it cannot determine was LocationId is in the 4th line. I verified using Kusto's explain keyword that I'm writing the correct Kusto query. So what's the problem ?

2

2 Answers

5
votes

if you want to have LocationId as one of the aggregation keys, you should include it in the call to summarize, as follows: | summarize ErrorCount = count() by UserId, LocationId.

[otherwise, please clarify the output schema you're expecting (ideally, alongside providing a sample input data set, using the datatable operator: datatable operator

1
votes

Just a friendly reminder of your original SQL code

select UserId, LocationId, COUNT(*) as ErrorCount from SampleTable where ResultType != 
'Success'
group by UserId
order by ErrorCount desc

which might contain an error:

LocationId is missing from the GROUP BY clause.

Correct SQL code should be:

select UserId, LocationId, COUNT(*) as ErrorCount from SampleTable where ResultType != 
'Success'
group by UserId, LocationId
order by ErrorCount desc

I think this might be the reason why you accidentally missed LocationId from the summarize clause in the Kusto code.