Calculating distinct counts with multiple conditions

[bhumikajpatel]

I am trying to compute distinct counts of a field based on multiple conditions. Can anyone please help with the calc that would help to compute distinct counts?

Example:
ID Index Index(2)
1 CA A
1 NY A
1 NY A
2 CA B
2 CA B
3 CA A
3 NY A
3 NY A

I am trying to get the distinct count of ID by Index(2) where Index(1) = "CA". Any help will be highly appreciated. thanks.

[somesoni2 · Accepted solution]

Give this a try

your current search giving fields ID, Index, and Index(2)
| eval CA_ID=if(Index="CA",ID,null())
| stats count(ID) as count dc(ID) as IDs dc(CA_ID) as CA_IDs by "Indexe(2)"

[bhumikajpatel]

Awesome! Many thanks. This worked perfectly... I was using the same but in if condition I was not ID for true value and hence was not getting correct results.

thanks for the help

[TISKAR]

Can you try this please:

   <yourbasesearch> | stats count(ID) dc(ID) dc(eval(!isnull(ID) AND index="CA")) as val3  by Index(2)  

[TISKAR]

@bhumikajpatel, are you test my proposition?

[bhumikajpatel]

Hi, Yes I did.. and I had tried this approach earlier as well... It gives my count as 1 for instead I needed the distinct count of Ids. Thanks for the help.

[elliotproebstel]

I think this will fit your needs:

your current base search
| search Index(1)="CA"
| stats dc(ID) AS ID_count BY Index(1)

You could also push that Index(1)="CA" into the base search, presumably.

[bhumikajpatel]

Apologies, I was not clear in my original post...

I need two types of counts.

COUNT(ID), DISTINCT_COUNT(ID) by INDEX(2), and DISTINCT_COUNT(ID) by INDEX(2)but with Index ="CA" as the condition.

Can we have such combined counts? thanks for the help.