How to use appendpipe to sum count and calculate correct percentage?

[richtate]

I have a query that works, but the output calculates a percentage column in a chart.  I need to show the total of TAM and the correct percentage value for all the returned rows.  I'm using this:

| inputlookup Patch-Status_Summary_AllBU_v3.csv
| stats count(ip_address) as total, sum(comptag) as compliant_count by BU
| eval patchcompliance=round((compliant_count/total)*100,1)
| fields BU total compliant_count patchcompliance
| rename BU as Domain, total as TAM, patchcompliance as "% Compliance"
| appendpipe
[stats sum(TAM) as TAM sum(compliant_count) as compliant_count
| eval totpercent=round((comp/TAM)*100,1)]
| eval TAM = tostring(TAM, "commas")

The output is:

Domain TAM compliant_count % Compliance

BU1	1,180	1146	97.1
BU2	2,489	2420	97.2
BU3	409,881	96653	23.6
BU4	3	3	100.0
BU5	1,404	1375	97.9
BU6	119,003	90100	75.7
BU7	33,506	30669	91.5
BU8	2,862	1997	69.8
BU9	239,897	216401	90.2
BU10	3,945	3832	97.1
BU11	569	482	84.7
	814,739	445078

If I add to the appendpipe stats command avg("% Compliance") as "% Compliance" then it will not take add up the correct percentage which in this case is "54.6" but the average would display "87.1".

How do I calculate the correct percentage as a total using the totals of columns TA

[richtate]

Thanks @johnhuang, that worked exactly like I needed it to!

[johnhuang · Accepted solution]

You don't need to use appendpipe for this. You can simply use addcoltotals to sum up the field total prior to calculating the percentage.

| inputlookup Patch-Status_Summary_AllBU_v3.csv
| stats count(ip_address) as total, sum(comptag) as compliant_count by BU
| addcoltotals labelfield=BU label=TOTAL total compliant_count
| eval patchcompliance=round((compliant_count/total)*100,1)
| fields BU total compliant_count patchcompliance
| rename BU as Domain, total as TAM, patchcompliance as "% Compliance"