Is a Case Statement using COUNT / SUM possible?

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

Find more posts tagged with

Accepted answers

RGranada

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.

All comments

RGranada

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.

John-Peddle

@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

@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

BlueRooster

@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.