Summarize Aggregate Functions in Kusto Query Language | Kusto Query Language (KQL)

 Topic: Summarize Aggregate Functions in Kusto Query Language | Kusto Query Language (KQL)

In this video we are going to learn about summarize  so summarize produce a table that aggregates the contents of input table with summarize we will be using a lot of functions such as count some and different other ones. Kusto Query Language is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQL's: databases, tables, and columns.

 //summarize -- Produces a table that aggregates the content of the input table.  
 //  
 //Let's create a table Customer  
 //.drop table Customer  
 .create table Customer (CustomerId: long, FName: string,LName:string, Salary:int,hiredate:datetime)   
  .ingest inline into table Customer <|  
 1,Aamir,Shahzad,2000,2021-11-28T04:52:43.8745893Z  
 2,Raza,ALI,4000,2018-11-28T04:52:43.8745893Z  
 3,Lisa,Rivers,50000,2021-12-28T04:52:43.8745893Z  
 4,steve,Ladson,1000,2010-09-28T04:52:43.8745893Z  
 5,Robert,Jr,500,2015-01-20T04:52:43.8745893Z  
 6,aamir,ali,1000,2005-03-01T04:52:43.8745893Z  
 // Get Count of all records ( Count of CustomerId)  
 Customer  
 | summarize TotalRows=count(CustomerId)  
 // get Sum of all salaries  
 Customer  
 | summarize TotalSalaryamount=sum(Salary)  
 // Get Count of All rows and Sum of Salary  
 Customer  
 | summarize CountofEmployees=count(),Totalsalary=sum(Salary)  
 // Get Count by Region  
 TotalSale  
 | summarize SalePersonCnt=count() by Region  
 // Group by Multiple Columns   
 TotalSale  
 | summarize CountofItems=count() ,sum(ItemsSold) by Region,Country  
 //using arg_max() -- > Finds a row in the group that maximizes ExprToMaximize, and returns the value of ExprToReturn (or * to return the entire row).  
 // Let's say if you want to find out in each of the region , which country has sold the most(max) Items.  
 TotalSale  
 | summarize arg_max(ItemsSold, Country) by Region  
 //using arg_min() -- >Finds a row in the group that minimizes ExprToMinimize, and returns the value of ExprToReturn (or * to return the entire row).  
 // Let's say if you want to find out in each of the region , which country has sold the least(minimum) Items.  
 TotalSale  
 | summarize arg_min(ItemsSold, Country) by Region  
 // using avg() - Calculates the average (arithmetic mean) of Expr across the group.  
 // Find out Avg sold price for each of product  
 TotalSale  
 | summarize avg(SoldPrice) by ProductName  
 //using max  
 TotalSale  
 | summarize  max(SoldPrice)  
 // using min   
 TotalSale  
 | summarize min(SoldPrice)  
 // get min sold price by Region  
 TotalSale  
 | summarize min(SoldPrice) by Region  
 // using sum -- Getting Sole price of all items  
 TotalSale  
 | summarize sum(SoldPrice)  
 // get sold price by Product  
 TotalSale  
 | summarize sum(SoldPrice) by ProductName  
 // take_any -- Arbitrarily chooses one record for each group in a summarize operator,  
 // and returns the value of one or more expressions over each such record.  
 TotalSale  
 | summarize take_any(ItemsSold) by Region,Country  
 // make_set --> Returns a dynamic (JSON) array of the set of distinct values that Expr takes in the group.  
 TotalSale  
 | summarize make_set(Region)  
 //dcount - Returns an estimate for the number of distinct values that are taken by a scalar expression in the summary group.  
 TotalSale  
 | summarize count(City), dcount(City)   
 //make_list() -- Returns a dynamic (JSON) array of all the values of Expr in the group.  
 TotalSale  
 | summarize make_list(Region)  
 TotalSale  
 | distinct Region  
 | summarize make_list(Region)  
 // helpful link  
 https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/hll-aggfunction  

Video Demo: Summarize Aggregate Functions in Kusto Query Language