Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

Equivalent to SQL's HAVING?


#1

I’d like to do get a count of unique values of a custom attribute having more than one unique value for another custom attribute. Something like, “Number of users who submitted this form more than once in the past day.”

Is that possible with Insights? If not, is there another way to approach it with Insights that would help me draw conclusions about whether that situation happens often?

Thanks!


New Relic edit

  • I want this, too
  • I have more info to share (reply below)
  • I have a solution for this

0 voters

We take feature ideas seriously and our product managers review every one when plotting their roadmaps. However, there is no guarantee this feature will be implemented. This post ensures the idea is put on the table and discussed though. So please vote and share your extra details with our team.


#2

Hi @joefriedl. I don’t know that NRQL has a SQL HAVING equivalent, but it sounds like (just judging from your use case) you might be able to achieve the following with this kind of query:

SELECT uniqueCount(users) FROM [event] WHERE formSubmissions > 1

Using the WHERE clause in addition to an operator like > will allow you to determine the total count of unique users who’ve performed a certain action more than once.

Let me know if this helps! I hope it does.

Thanks,

Henry


#3

Thanks @hshapiro.

I’d like it if we were able to send the count to NewRelic ourselves, but that won’t work out too well in our situation. I can provide a little more context about what we’re trying to do.

Each user has their own URL they’re making requests to, and that URL has a resourceID attribute associated with it. For these requests we have two possible values of an outcome attribute, we’ll call them “success” and “failure”.

The specific question we’re trying to answer is: How often does a “success” follow a “failure”? In other words, are users trying again, and do they eventually succeed?

So what we’d like to be able to see is how many of those unique resourceIDs have both a “success” and a “failure” event.

I hope that clarifies things a bit. Does that sound like something Insights could provide… insight… into?

Thanks again!


#4

In the where clause you should be able to an AND so that you can include searches for both your success and failures.

If this doesn’t quite do what you are trying, perhaps you can send a custom attribute that logs when a success happens after a failure.

Let us know if this still doesn’t quite get you there.


#5

I’d like to hop on this with the more general question of how to limit results returned from a count(*) query to only list those with counts greater than a certain number? I’m trying to create a “busiest time of day” chart and this is my current NRQL:

SELECT count(*) FROM PageView FACET hourOf(timestamp) SINCE 1 month ago

How can I limit this to only show times that had more than, e.g., 5 views (low usage site)? Is this possible? Or perhaps there is a better approach?


#6

I saw this and thought what an interesting challenge! But I agree that while you get get the information being able to have a HAVING clause correlated to the select would be a good feature. So the query might read

SELECT count(*) AS 'Visits' FROM PageView FACET hourOf(timestamp) SINCE 1 week ago HAVING Visits > 1000

You could also achieve a similar thing if you could order by, in my example, Visits and using the LIMIT command. For example:

SELECT count(*) AS 'Visits' FROM PageView FACET hourOf(timestamp) SINCE 1 week ago ORDER BY Visits LIMIT 3

Either way it gets my vote.

-Martin


#7

+1

I think NRQL and Insights are only about a year old, but some basic features seem missing. HAVING is important to me.

I’m trying to find out how many people are logging into my site’s Wordpress admin, and using it for more than 5 pageviews in the last 2 weeks. Here’s my process for now:

  • Run NRQL

      SELECT count(*) from PageView since 2 weeks ago where page_url like '/wp-admin/%' FACET wp_username limit 1000
    
  • Copy the text results to my text editor, and look at what line the last number is.

This of course can’t be automated well (well, it could if I were willing to write a script to parse the JSON results). But even if the results screen had line numbers, I could do it without having to copy to a text editor. How about a simple feature request of adding line (record) numbers to the results?


#8

Hi @tylercollier thanks for you input. I’ll go ahead and create a feature request to include record numbers to the results and have added you to feature request to include HAVING in NRQL queries.


#9

Is there somewhere I can vote on the feature request for a HAVING equivalent in NRQL. It’s been over three years and it still seems like this is not possible. I have a use case for it and can’t seem to figure out how to do it.


#10

No problem, @John.Hopson! Please feel free to vote above! :blush: The more votes we collect, the better!