Hello everyone,
I've read other related posts and tried with my case but still failed, see if anyone could help. Thanks in advance!
I try to get the % of certain rating (with 'green' or 'acceptable'), but I always get the result=2 with my formula. However, it returns the correct count when I do the numerator and denominator separately, but just failed to get the divided number. note: I need DISTINCT as the `Factory ID` in my case have mutliple records in data.
my formula is like this:
COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`)
fyi., I have also tried these as suggested in some posts but also failed to get the correct result:
i tried this:
case when COUNT(DISTINCT `Factory ID`)=0 then 0 else
(COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`)) end
and also either of these - both return a super large value:
case when COUNT(DISTINCT `Factory ID`)=0 then 0 else
(COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`) over ()) end
OR
(COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`) over ())
Much appreciate if anyone can help and share your thought! THANKS in advance!
regards,
Nek