count, distinct, if

[jisephcirspy]

So I want to count the entries where the state is 'CRIT', but distinct the entries with the same names.
It is a bit like this:

count(DISTINCT[name]) but with the condition If(state='CRIT', 1, 0))

If(state='CRIT', 1, 0))

count(DISTINCT[name]) 

[stevedark · Accepted solution]

Hi @jisephcirspy 

You have all the bits you need there, just need to put them together.

The thing you can use to your advantage is that the COUNT function (DISTINCT or otherwise) does not count NULLs, so you just have to put a null in where it is not the type you wish to count:

count(DISTINCT If(service_state='CRIT', alias, null()))

That should do the trick.

Steve

[jisephcirspy]

Thank you very much @stevedark !!! helped me a lot

[dieterwoestemeier · Accepted solution]

Try to use set analysis where possible because it performs way better than IFs.

In your case:

count({<state={'CRIT'}>} DISTINCT([name]))

[stevedark]

Hi @dieterwoestemeier 

D'oh! Focussed on answering the question in hand that I missed that there was a much better way of achieving the same thing.

Thanks for posting a better way.