Is a Case Statement using COUNT / SUM possible?

John-Peddle
John-Peddle Contributor
in Magic ETL

Good morning. Not sure if it's the case statement itself, i.e. incorrect syntax or if there's an issue related to divide a COUNT by a SUM value. We've also tried to run this as COUNT / COUNT but to no avail.

 

We'd like to use only certain agents when trying to determine a Completed Conversion % for offers. The beast mode currently used to determine COMPANY WIDE conversion rate is:

CASE
WHEN SUM(`Settled`) = 0 THEN 0
ELSE (SUM(`Completed`)/ SUM(`Settled`))
END

 

However, when determining the conversion rate for specific agents we'd need to isolate them using the OWNER column, which ties in their name with the COMPLETED column from the beast mode above, but we've come up with this but again it's not validating/working:

CASE (
WHEN COUNT(`Owner` = 'Jane Doe', 'John Doe')
THEN 1 ELSE 0 / SUM(`Settled`)

END)

 

** we've tried several variations on this as well, even using a single agent name, with no luck in getting it to validate/work

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

    If I understood correctly your question, this may help:

     

    COUNT(CASE WHEN `Owner` = 'Jane Doe' OR `Owner` = 'John Doe' THEN `Owner` END)

    /

    NULLIF(SUM(`Settled`),0)

     

    Tell me how it goes.

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • RGranada
    RGranada Contributor
    Answer ✓

    Hi,

    If I understood correctly your question, this may help:

     

    COUNT(CASE WHEN `Owner` = 'Jane Doe' OR `Owner` = 'John Doe' THEN `Owner` END)

    /

    NULLIF(SUM(`Settled`),0)

     

    Tell me how it goes.

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • John-Peddle
    John-Peddle Contributor

    @RGranada, sorry for taking so long to respond, and thanks for the quick reply, as always! It didn't work at first but then realized SETTLED is a date field and that it likely couldn't be totaled as a SUM, so we changed it to COUNT and it validated - seems to be working as expected!

  • John-Peddle
    John-Peddle Contributor

    @RGranada, before I do anything further, just wanted to see what we should do to expand upon our original request in this thread? Wasn't sure if we should add that in here, or open a new thread since this was already marked as resolved. Thanks in advance!  Smiley Happy

  • Valiant
    Valiant Coach

    @John-Peddle feel free to go ahead and open a new topic if you have questions around something else. That will help make your new request searchable for someone else if they have the same question later, as well as increasing visibilty for others to respond.

Categories