Sum column a x times with different columns in a single query

[rajg369]

e.g
how to get sum of below in single query
sum(val_2) by application
sum(val_2) by val_1

Query Result(single query)
column1      column2
ABC              1478
FSD               4839
A                    5849
B                    478

or

column1      column2  column3    column4
ABC              1478                A                5849
FSD               4839               B                 478

or what ever possible in single query

Please help

[gcusello]

Hi @rajg369,

you have many ways to reach your target, try e.g. this:

your_search
| stats sum(val_2) as column1 by application
| append [ search 
     your_search
     | stats sum(val_2) As column1 by val_1
     | rename val_1 AS column2
     ]
| sort column1

Ciao.

Giuseppe

[rajg369]

Thanks for you reply. I have to pass e.g e_batch_id of a subquery to all the append query how to do?

right now the first stats get the correct batchid from subquery and produces correct sum, the append query does not and picks random batchid and the sum is not correct. all append query should run for same batchid from the subquery. Please help

index=primary source="x.log.*" e_host_env="UAT" 
    [ search index=primary source="x.log.*" e_host_env="UAT" 
    | stats sum(e_total_time) as TotalTime by e_batch_id
    | sort TotalTime desc
    | head 1
    | table e_batch_id]
| stats sum(e_Val_count) as ValCount, sum(e_total_time) as ValTime by e_batch_id,e_current_batch_num
| append
     [ search
        index=primary source="x.log.*" e_host_env="UAT" 
        | stats sum(e_total_time) as StreamTime by e_batch_id, e_stream
    ]

[ITWhisperer]

| makeresults
| eval _raw="application,val_1,val_2
ABC,A,1000
ABC,B,478
FSD,A,4839"
| multikv forceheader=1
| table application val_1 val_2
| eventstats sum(val_2) as sum_1 by val_1
| eventstats sum(val_2) as sum_application by application
| eval name_value=application."|".sum_application
| eval name_value=mvappend(name_value,val_1."|".sum_1)
| stats count by name_value
| eval column_1=mvindex(split(name_value,"|"),0)
| eval column_2=mvindex(split(name_value,"|"),1)
| table column_*