Splunk Search
Highlighted

## How can I create a query where I can sum the total and then take the percentage and add them in a column?

Engager

Hello,

I need your help for the following:
I need to add the Total row and then divide it by the column of funds.
Example total 559892+32398=592190/funds consecutive
and add the percentage in another column that is called% of total

My Query is the following

``````search........
| rex field=Cuenta (?<BIN6>\d{6})
| lookup  Bines_Lookup BIN as BIN6 OUTPUT DES as Descripcion_BIN TTARGETA as TTARGETA
| search Descripcion_BIN!="N/A"
| bucket _time span=2h
| chart limit=0 count by  BIN6 Respuesta
| addtotals fieldname=Approved Approved* Honor* Partially*
| addtotals fieldname=Rejected Refer* Allowable* Do* Error* Collect* No* Customer* Retry* Transaction* Bank* Partially* Expired* Suspicion* Call* Restricted* all*  Lost* Stolen* Not* Incorrect* Exceeds* Hard* Response* Issuer* Financial* Duplicate* PTLF* Bad* Pick* Invalid* Requirement* Suspicious* File* Format* It* Reserved*
| table BIN6 Approved Rejected Others "Not sufficient funds"
| eval "% Aceptation" = round(Approved/(Approved+Rejected)*100, 2)
| rename "Not sufficient funds" as fondos
| table BIN6 Approved Rejected "% Aceptation"  fondos
| addcoltotals label=Total Approved Rejected labelfield=BIN6
|  eval "% of total"=(Total/fondos)*10
| table BIN6 Approved Rejected "% Aceptation"  fondos "% of total"
``````
Tags (4)
1 Solution
Highlighted

## Re: How can I create a query where I can sum the total and then take the percentage and add them in a column?

Super Champion

row of funds =5000 how you have get this?

Highlighted

## Re: How can I create a query where I can sum the total and then take the percentage and add them in a column?

Engager

excuse me wrong, I need the total sum first and then divide it for each row of funds. Example
592190/2, 592190/47
to have a new column

Highlighted

## Re: How can I create a query where I can sum the total and then take the percentage and add them in a column?

Champion

Hi,

Are u talking about something similar to this solution provided in this question?

Highlighted

Engager

thank you !

Highlighted

## Re: How can I create a query where I can sum the total and then take the percentage and add them in a column?

Super Champion

Try this:

``````...|appendpipe[|search BIN6=total|eval Sum=Approved + Rejected ]|eventstats sum(Sum) as summation|eval "% of total" =summation*100/test."%"
``````

let me know if it helps!

Highlighted

## Re: How can I create a query where I can sum the total and then take the percentage and add them in a column?

Contributor

Hi,

Start by removing this :

`````` | addcoltotals label=Total Approved Rejected labelfield=BIN6
|  eval "% of total"=(Total/fondos)*10
| table BIN6 Approved Rejected "% Aceptation"  fondos "% of total"
``````

And replace it by this :

``````| eventstats sum(Approved) as tot_aproved sum(Rejected) as tot_rejected
| eval Total = tot_approved + tot_rejected
| eval "% of total" = (Total / fondos) * 10
| table ...
``````

This sould work well !

Highlighted

## Re: How can I create a query where I can sum the total and then take the percentage and add them in a column?

Engager

Thank you for help. I worked the solution