1

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?

Diane Kaplan
  • 1,626
  • 3
  • 24
  • 34

3 Answers3

4

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thanks! This sounds like we're getting close(!), but that gives "unexpected 'when'" NRQL Syntax Error- so close and yet so far... – Diane Kaplan Aug 17 '20 at 21:22
0

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

Gabriel Furstenheim
  • 2,969
  • 30
  • 27
  • Thank you! I hadn't heard of CTEs yet, and this is exactly the sort of thing I need. Looking at the syntax there, I'm betting this will be slightly different for various databases/settings, so I was probably wrong to frame it as a SQL question, and will edit to be NRQL – Diane Kaplan Aug 17 '20 at 19:32
-1

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;
Atif
  • 2,011
  • 9
  • 23