How to add results from stats value?

[n4niyaz]

Hi

I have a field called department, on that field i have multiple values like

department=Production for Medicine 
department=Research and Development
department=Purchasing
department=Marketing
department=Human Resource Management
department=Accounting and Finance 
department=Production for Medicine-PFM
department=Research and Development-RD
 So after running stats count by department am getting below results like

department                                  count

Production for Medicine                   5
Research and Development             10
Purchasing                                          12
Marketing                                             6
Human Resource Management        3
Accounting and Finance                      4
Production for Medicine-PFM             6
Research and Development-RD         13

What I need is to add count value of following:

Production for Medicine + Production for Medicine-PFM =11
Research and Development + Research and Development-RD  =23

Kindly let me know if there is any solution to get above result.

[TISKAR]

Hello,

Try this please:

| eval PFM =if(departement="Production for Medicine " OR departement="Production for Medicine-PFM",count,0)
| eventstats sum(PFM) as sum
|appendpipe[departement="Production for Medicine + Production for Medicine-PFM", count=sum]
| fields -sum
| dedup departement 

[kmaron]

You could drop everything after and including the dash from the department name using a regex

| rex field=department mode=sed "s/-.*$//"

[n4niyaz]

Thanks for the reply but i need is sum of total count of Production for Medicine + Production for Medicine-PFM =11 in a separate field either PFM=11 means count of Production for Medicine(6) + Production for Medicine-PFM(5)=PFM ie PFM =6+5 ie PFM=11 and here
Research and Development (10)+ Research and Development-RD(13) =23 RD=23 ie RD=10+13 ie RD=23

[kmaron]

try this:

| rex field=department "(?<base_department>.*)-\S"
| stats count by department, base_department
| appendpipe [stats sum(count) as count by base_department]
| sort base_department
| fields - base_department