Search code examples
azure-data-explorerkql

Summarize count() multiple columns with where clauses

I'm trying to get the count of multiple things in a Kusto query but having trouble getting it working. Let's say I have a sample table like this:

let SampleTable = datatable(Department:string, Status:string, DateStamp:datetime) 
[
   "Logistics", "Open", "05-01-2019",
   "Finance", "Closed", "05-01-2020",
   "Logistics", "Open", "05-01-2020"
];

And I query like this:

SampleTable
| summarize closedEntries = count() by (Status | where Status == "Closed"), 
    openEntries = (Status | where Status == "Open"),
    recentDates = (DateStamp | where DateStamp > "12-31-2019"),
    Department

Expected results:

expected results

But this gives an error "The name 'Status' does not refer to any known column, table, variable or function." and the same error for DateStamp. I've also tried using extend and join but it's a mess.

Solution

  • you could use the countif() aggregation function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/countif-aggfunction

    datatable(Department:string, Status:string, DateStamp:datetime) 
[
   "Logistics", "Open", "05-01-2019",
   "Finance", "Closed", "05-01-2020",
   "Logistics", "Open", "05-01-2020"
]
| summarize closedEntries = countif(Status == "Closed"),
            openEntries = countif(Status == "Open"),
            recentDates = countif(DateStamp > datetime(12-31-2019))
         by Department