CODEWITHSUNDEEP
azure-data-explorerkql
SendETHToThisAddress
SendETHToThisAddress

Reputation: 3704

Kusto summarize 3 or more columns

Is there a way to use summarize to group 3 or more columns? I've been able to successfully get data from 1 or 2 columns then group by another column, but it breaks when trying to add a 3rd. This question asks how to add a column, but only regards adding a 2nd, not a 3rd or 4th. Using the sample help cluster on Azure Data Explorer and working with the Covid19 table, ideally I would be able to do this:

Covid19
| summarize by Country, count() Recovered, count() Confirmed, count() Deaths
| order by Country asc

And return results like this

Expected results

But that query throws an error "Syntax Error. A recognition error occurred. Token: Recovered. Line: 2, Position: 36"

Upvotes: 2

Views: 4725

Answers (1)

SendETHToThisAddress
SendETHToThisAddress

Reputation: 3704

I had the right basic idea, you just can't use count repeatedly inline like that. You can use sum, dcount, or max:

Covid19
| summarize sum(Recovered), sum(Confirmed), sum(Deaths) by Country
| order by Country asc

Another example:

Covid19
| where Timestamp == max_of(Timestamp, Timestamp)
| summarize confirmedCases = max(Confirmed), active = max(Active), recovered = max(Recovered), deaths = max(Deaths) by Country
| order by Country asc

In this example I'm getting the latest data for each of the selected columns. Since I initially used the where clause to get the latest data you would think I could just list the columns, but when using summarize you have to use an aggregate function so I used max on each column

Upvotes: 2

Related Questions