How do you select rows with only non-empty field values?

Hello!

I am currently running into an issue with our data where some of our events don’t have a certain field (called errorCode). My problem is when I am trying to calculate a success rate, I can’t ignore failures from an event that don’t have an errorCode.

I know that if I do something like:

SELECT count(*) FROM login_errors FACET errorCode

Then the events with no error code will be removed. However, how can I do that when I can’t use a FACET on errorCode because I am calculating a different value that I don’t want to break up by errorCode?

I thought I could do something like

SELECT count(*) from login_errors WHERE errorCode!=’’ OR errorCode!=NULL

but neither of those options works. Any help?

Hi, @Caleb.Taylor:

SELECT count(*) FROM login_errors WHERE errorCode != '' AND errorCode IS NOT NULL
1 Like

Awesome, thank you! Sorry that was simpler than I thought. I do have something that may be a bit more complex, though.

I am calculating a login success %, so I have a query like so:

SELECT (uniqueCount(login_success.id)) / (uniqueCount(login_success.id) + uniqueCount(login_failure.id)) * 100 as ‘Success %’ FROM login_success, login_failure WHERE errorCode != ‘UNAUTHORIZED’ SINCE 30 minutes ago UNTIL now

The main issue I have, is I only want to include events from “login_failure” if they have a non-empty error code. How do I apply that condition to this query?

Obviously, applying “errorCode IS NOT NULL” to the WHERE statement fails because the errorCode field is not in the “login_success” event.

Thanks in advance!

Edit:
I’ve also tried:

SELECT (uniqueCount(login_success.id)) / (uniqueCount(login_success.id) + uniqueCount(login_failure.id)) * 100 as 'Success %' FROM login_success, login_failure WHERE login_failure.errorCode != 'UNAUTHORIZED' AND login_failure.errorCode IS NOT NULL SINCE 30 minutes ago UNTIL now

Try this:

SELECT 
  (uniqueCount(login_success.id) / 
   uniqueCount(login_success.id) + filter(uniqueCount(login_failure.id), WHERE errorCode != '' AND errorCode IS NOT NULL)) * 100 AS 'Login Success %'
FROM login_success, login_failure

A cleaner solution would be to have a single Login event, with a result attribute containing either success or failure. Then you could calculate success rate like this:

SELECT 
  filter(count(*), WHERE result = 'success') / 
  filter(count(*), WHERE result = 'failure')
FROM Login
WHERE errorCode != '' AND errorCode IS NOT NULL
3 Likes