The data

I have a table as the following in Google Sheets:

Month	Country	Metric Name	Value
Nov	AAA	Metric_1	98
Nov	AAA	Metric_2	45
Nov	AAA	Metric_3	4
Nov	BBB	Metric_1	100
Nov	BBB	Metric_2	214
Nov	BBB	Metric_3	13
Nov	CCC	Metric_1	75
Nov	CCC	Metric_2	84
Nov	CCC	Metric_3	21
Nov	Worldwide	Metric_4	3
Nov	Worldwide	Metric_5	87
Oct	AAA	Metric_1	94
Oct	AAA	Metric_2	41
Oct	AAA	Metric_3	0
Oct	BBB	Metric_1	96
Oct	BBB	Metric_2	210
Oct	BBB	Metric_3	9
Oct	CCC	Metric_1	71
Oct	CCC	Metric_2	82
Oct	CCC	Metric_3	17
Oct	Worldwide	Metric_4	-1
Oct	Worldwide	Metric_5	83

Objective

The end goal is to have a table summarizing each metric per month, ideally just the averages:

Month	Metric_1	Metric_2	Metric_3	Metric_4	Metric_5
Nov	91	114.33	12.66	3	87
Oct	87	109.33	8.66	-1	83

Failed attempts

My first attempt was using a multitude of VLOOKUP functions, but the formulas were only getting messier so I dropped that approach.

I discovered the QUERY function and 'Google Visualization API Query Language'. This code works when considering only one metric:

 QUERY(my_table,"
  SELECT Col1, AVG(Col4)
  WHERE Col3 = 'Metric_1'
  GROUP BY Col1
  LABEL AVG(Col4) 'Metric_1'
",1)

Month	Metric_1
Nov	91
Oct	87

However, I cannot seem to find how to apply different conditions per column. I was wondering if it's possible to integrate a function like IF() or AVERAGEIF() in the SELECT part of the query. Something like:

 QUERY(my_table,"
  SELECT Col1,
         AVERAGEIF(Col3,'=Metric_1',Col4),
         AVERAGEIF(Col3,'=Metric_2',Col4),
         AVERAGEIF(Col3,'=Metric_3',Col4),
         AVERAGEIF(Col3,'=Metric_4',Col4),
         AVERAGEIF(Col3,'=Metric_5',Col4),
  GROUP BY Col1
",1)

How can the summary table be obtained in a single query?

CodePudding user response：

use:

=QUERY({A:D};
 "select Col1,avg(Col4)
  where Col3 is not null
  group by Col1
  pivot Col3")