How to create total average/median/max of field in a separate table?

[LearningGuy]

How to create total average/median/max of field in a separate table?
Thank you in advance

| index=testindex
| table company, ip, Vulnerability, Score

company	ip	Vulnerability	Score
CompanyA	ip1	Vuln1	2
CompanyA	ip1	Vuln2	0
CompanyA	ip2	Vuln3	4
CompanyA	ip2	Vuln4	2
CompanyA	ip3	Vuln5	3
CompanyA	ip3	Vuln6	5

Group by IP  => This worked just fine
| stats values(company), avg(Score) as AvgScore by ip

company	ip	AvgScore
CompanyA	ip1	1
CompanyA	ip2	3
CompanyA	ip3	4

Group by Company   =>  how do I group by company after group by ip (using stats) and put it on a separate table?
| stats avg(AvgScore) as Average, avgAvgScore) as Median, max( AvgScore) as Max by company

Company	Average	Median	Max
CompanyA	2.7	3	4

[LearningGuy]

Hello,
I tried your suggestion and it worked fine. I am accepting this as a solution
Can you also suggest how to put the average, median and max on the bottom of the table?
Thank you again
Below is the example:  

company	ip	AvgScore
CompanyA	ip1	1
CompanyA	ip2	3
CompanyA	ip3	4
	Average	2.7
	Median	3
	Max	4

[richgalloway]

I think you can do that with the appendpipe command, which processes the current results and adds new results to bottom.

| stats values(company), avg(Score) as AvgScore by ip
| appendpipe [ stats avg(AvgScore) as Average, median(AvgScore) as Median, max(AvgScore) as Max by company ]

[LearningGuy]

Hello,

I tried the command you suggested and it did not show any effects
Please suggest.

Thanks

[richgalloway]

I used the wrong case in the field name.  Try my edited answer.

[LearningGuy]

Hello,
There would be no difference because I converted your suggestion to my real data, so I already fixed any details
Please suggest.

Thanks,

Marius

[richgalloway · Accepted solution]

A separate table requires a separate search.

If this is in a dashboard then consider making the first table a base search and the second table a post-processing of the first.  That will save you time and resources when the dashboard runs.

...
<search id="base"> <!-- "base" can be any string -->
  <query>| index=testindex
| table company, ip, Vulnerability, Score
| stats values(company), avg(Score) as AvgScore by ip</query>
...
</search>
...
<search base="base"> <!-- Use the same string as above -->
<query>| stats avg(AvgScore) as Average, avgAvgScore) as Median, max( AvgScore) as Max by company</query>
</search>
...