Get an average of sums

I am new to NQRL but very experience in SQL. I think that is causing me to make mistakes.

I have a Custom Value that I call SessionId. Service calls from my application add this value to the New Relic data for the call.

I use the following NRQL to get the total duration of all the calls in each SessionId:

SELECT sum(duration) from Transaction where http.SessionId is not null FACET http.SessionId

This works great. I get a list of the SessionIds and how long each one took.

I then want to know the average so I can put a single number on a dashboard. (To be able to show the average session time.)

I was expecting something like this to work:

SELECT average(SELECT sum(duration) from Transaction where http.SessionId is not null FACET http.SessionId)

But that gives the error “Unexpected Select”.

I can’t think I am the first person to go for an average of sums in Insights, but I don’t know how to get there from here.

What is the NRQL way to get an average of my total durations?

Hi, @stephen.schaff: No, you are not the first person to discover that NRQL does not support subqueries or nested functions. :slight_smile:

Unfortunately it is not currently possible to do what you are trying to do. You may wish to add your voice to the following feature idea:

2 Likes

Wow! So an average of sums is not supported? I have to admit I find that surprising!

I am only evaluating New Relic at this time, but a limitation like this goes in the “negative” column for our scorecard.

Hey @stephen.schaff - Thanks for the feedback! I understand that not being able to nest functions like SELECT average(sum(attribute)) is far from ideal. We’ll get your +1 added in to our internal feature request.

An update to this thread: NRQL now supports nested queries! Please see https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/nrql-query-tutorials/nested-aggregation-make-ordered-computations-single-query for more information.