Help with Beastmode on Count with Condition

Hello, I feel like this should be a simple thing to do, but am stumped.

I am trying to do a count of order numbers where there is a condition to be met. Is there a if function that I can follow to do this?

For example, there is a list of vendors, some of which have registered and some have not. I want to count the # of orders the vendor has for 1) if they are registered and 2) if they are not registered in two separate columns.

I tried a few things, but can't seem to get it..

1) if 'isRegistered' = 'True' then count(OrderNo)

2) case when 'isRegistered' = 'True' then count(OrderNo) else 0 end. This step kind of works but whenever 'isRegistered' = 'False', it will just display as 0.

Thank you!

Answers

  • GrantSmith
    GrantSmith Indiana 🟣
    edited April 8

    Hi @user077529

    You can use a CASE statement to conditionally count values in a beast mode. You're close but yours is only counting everything if isRegistered is true. You want something like this:


    Registered count

    COUNT(CASE WHEN `isRegistered` = 'True' THEN `OrderNo` END)
    

    Non-Registered.

    COUNT(CASE WHEN `isRegistered` = 'False' THEN `OrderNo` END)
    


  • user077529
    user077529 ⚪️
    edited April 8

    Hey Grant,

    That's really helpful, thank you! Is there a way to do this if the value is null? For example...

    count(case when 'isRegistered' = NULL then 'OrderNo' END)

    And vice versa is there a way to count OrderNo if 'isRegistered' has ANY populated value, for example:

    count(case when 'isRegistered' not NULL then "OrderNo' END)

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟣

    @user077529

    you would do isRegistered IS NULL and isregistered IS NOT NULL when evaluating nulls.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!