I have the following code and works but I want to SUM as is I get 3 values

Absher_645
Absher_645 Member
in Beast Mode

CASE WHEN OrderReason='Disconnect' THEN COUNT(DISTINCT AccountCode) *-1
WHEN OrderReason='New Service' THEN COUNT(DISTINCT AccountCode) *1
WHEN OrderReason='New Account' THEN COUNT(DISTINCT AccountCode) *1
END

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Absher_645 You will need break your calculation into two separate case statements surrounded by a count distinct, one for positive values and one for negative values:

    count(DISTINCT case when `OrderReason` in ('New Service','New Account') then `AccountCode` end)
-
count(DISTINCT case when `OrderReason` = 'Disconnect' then `AccountCode` end)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Absher_645 You will need break your calculation into two separate case statements surrounded by a count distinct, one for positive values and one for negative values:

    count(DISTINCT case when `OrderReason` in ('New Service','New Account') then `AccountCode` end)
-
count(DISTINCT case when `OrderReason` = 'Disconnect' then `AccountCode` end)

Categories