CODEWITHSUNDEEP
azure-data-explorerkql
Mr John
Mr John

Reputation: 241

Overall Avg and Avg. by in same query

Can I receive help on how to create a sql that includes the below?

I have two metrics that I want to show in the same query.

  1. Avg
  2. Avg summarized by

This should look something like the below table:

 Endpoint    OverallAvg     AvgbyEndpoint
 Red           25,000         20,000
 Green         25,000         30,000
 Yellow        25,000         25,000

My current code looks something like:

 let overallavg =
 requests 
 |extend  
 Endpoint = tostring(split(name, "/",2)[0])
 |summarize AvgDuration_seconds= avg(duration);
 requests
 |extend 
 Endpoint = tostring(split(name, "/",2)[0])
 |summarize AvgDuration_seconds= avg(duration) by Endpoint, overallavg

Upvotes: 1

Views: 51

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you could try this:

let T = 
   requests
   | extend Endpoint = tostring(split(name, "/",2)[0])
;
let _overallAverage = toscalar(
   T
   | summarize avg(duration)
);
T
| summarize AvgByEndpoint = avg(duration) by Endpoint
| extend OverallAverage = _overallAverage

Upvotes: 1

Related Questions