CODEWITHSUNDEEP
sqlnewrelicnrql
Diane Kaplan
Diane Kaplan

Reputation: 1708

NRQL percentage of one subset over another

I have a great big eventing table with lots of different types of events, and I'm working on a New Relic graph to show the percentage of one kind of event to another kind of event. (NOT percentage over the total row count in the table).

So in this example Segment table:

id  name
1   foo      
2   bar
3   baz
4   bar

I'd get the foo events with a query like this:

select count(*) from Segment where name='foo'

My question: how would I get the percentage of foo events to bar events? I've poked around trying to join two queries that save each "as" a particular name, but no luck yet. Does anyone know if/how we can make Common Table Expressions for use in a query/graph?

Upvotes: 1

Views: 3599

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use conditional aggregation:

select (sum(case when name = 'foo' then 1.0 else 0 end) /
        sum(case when name = 'bar' then 1.0 else 0 end)
       ) as foo_bar_ratio
from segment;

EDIT:

Perhaps this will work in NRLQ:

select filter(count(*), where name = 'foo') / filter (count(*), where name = 'bar') as foo_bar_ratio
from segment;

Upvotes: 4

Atif
Atif

Reputation: 2210

You can make use of PIVOT, this should work in oracle and mssql but not sure about NRQL.

DEMO

select (FOO_COUNTER/BAR_COUNTER) * 100 from  (
select * from table1
pivot(count(id) as counter for name in ('foo' foo, 'bar' bar))) x;

Upvotes: -1

Gabriel Furstenheim
Gabriel Furstenheim

Reputation: 3432

To use ctes, you need to refer to it twice. One for the value for green, one for the value for red. For example (first one is to load the data):


with mock as (select * from (values (1, 'red'), (2, 'green'), (3, 'blue'), (4, 'green')) as mock(id, color)),
     aggregated as (
         select count(*), color
         from mock
         group by color
     )

select ag1.count::float / ag2.count::float, ag1.color
from aggregated ag1,
     aggregated ag2
where ag2.color = 'green'

I'm using postgres here

Upvotes: 0

Related Questions