NRQL using the FACET count value as a filter

Question

I am hoping to FACET values together and then get a count of those values that have > 2 results. I have this part.

SELECT count(*) as 'requestCount' FROM Transaction WHERE FACET SomeId

This returns

ID1 - 3
ID2 - 1
ID3 - 5
ID4 - 1
ID5 - 7

But I only want to know how many came back with a 'requestCount' > 2 .

So I only want to know that 3 items were above 2. I don't even want to know the ID values only the roll up figure.

Is this possible ?

Answer 1

It should be possible with nested aggregation. There is an example there about average CPU that looks pretty similar to what you are looking for.

Maybe something like:

SELECT count(*) FROM (
  SELECT count(*) as 'requestCount' FROM Transaction WHERE FACET SomeId
) WHERE requestCount > 2