How to achieve stats count by field showing fields with zero?

[denissotoacc]

Let's suppose I have the following search:

| makeresults 
| eval name="Denis", age=34
| append 
    [| makeresults 
    | eval name="Nazarena", age=28]
| append 
    [| makeresults 
    | eval name="Diego", age=10]
| append 
    [| makeresults 
    | eval name="Maria", age=43]
| search age > 30
| stats count by name

It outputs:

name	count
Denis	1
Maria	1

I need to get the number of times some name appears when it's age is higher than 30 BUT I need to show the unmatched names (lower than 30) as "count = 0". Something like this:

name	count
Denis	1
Nazarena	0
Diego	0
Maria	1

What should I need to change in this search in order to achieve that?

[gcusello]

Hi @denissotoacc,

adapt this to your needs:

| makeresults 
| eval name="Denis", age=34
| append 
    [| makeresults 
    | eval name="Nazarena", age=28]
| append 
    [| makeresults 
    | eval name="Diego", age=10]
| append 
    [| makeresults 
    | eval name="Maria", age=43]
| eval type=if(age>30,"higher","lower")
| stats dc(type) AS dc_type values(type) AS type count BY name

Ciao and Happy Easter.

Giuseppe

[tscroggins · Accepted solution]

@denissotoacc 

Instead of counting by name, try summing by a condition:

| stats sum(eval(if(age>30, 1, 0))) as count by name

[denissotoacc]

This is exactly what i needed. Thanks!