Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Kusto | 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.

like image 699
SendETHToThisAddress Avatar asked Dec 22 '20 23:12

SendETHToThisAddress


1 Answers

you could use the countif() aggregation function: https://docs.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
like image 146
Yoni L. Avatar answered Sep 28 '22 01:09

Yoni L.