Distinct count of multiple values from the same field

[kamaleshwar]

I have some fields "Codes" "Count". In the "Codes" field i'll get multiple values and will count the values totally by using "dc(Codes) as Count". But i need the unique count of each code.

For Ex.

Codes        Count
123             10
111
222
333
444
555

The above is showing us as total count of values, but i need the unique count of each values like

Codes        Count
    123             5
    111             1
    222             1
    333             1
    444             1
    555             1

Please help on this. If you have any questions please post. Thanks in advance!!!

[kamaleshwar]

Actually the result should be like this.

ID                        Codes        Count
example1            123            5
                              111            1
                              222            1
                              333            1
                              444            1
                              555            1

Example2            668            3
                              554            1
                              666            1

[javiergn]

See my second answer below.
Please let me know if that's not exactly what you are looking for.

[gyslainlatsa]

hi,
with this query, you have the answer

 your base search |stats dc(Codes) as Count_Codes by field

where field contains the values of the field codes

[kamaleshwar]

Thanks for your response! This is not exactly i want.

[Jeremiah]

Sounds like adding a by clause will give you what you need:

| stats count by code 

[kamaleshwar]

this one won't help if we have multiple user using multiple codes. I've added the sample result above.

[Jeremiah]

Ah ok, then try this one:

... | stats count by ID code | stats list(code) AS code list(count) AS count by ID

[kamaleshwar]

Thanks for your response! Actually it's not working. It's simply showing a empty space.

[javiergn]

Hi,

Simply do:

yoursearch
| stats count by Codes

And it'll give you the output you are looking for.

[javiergn]

If there's an ID simply do it this way:

 yoursearch
| stats count by Codes, ID

If you want to display it exactly the way you mentioned above then this is probably closer:

yoursearch
| stats count by Codes, ID
| stats list(Codes) as Codes, list(count) as count by ID

[kamaleshwar]

Thanks for your response! Actually it's not working. It's simply showing a empty space.