How to average multiple columns within a query?

Question

Google Sheet: https://docs.google.com/spreadsheets/d/1CKBxaRPP9KR6j3Gp24JRpuGOxUbirBuVg3riRcokQXI/edit?usp=sharing

Scroll to the bottom where I have Avg Days to Close and the Speedometers.

I am trying to get the Purchase Closes one (in Cell F500) to average out ColumnBY, ColumnBZ, ColumnCA from the BNTouch Raw Data Sheet using the query.

Here is what I have for the query, found in Cell F500:

=Query('BNTouch Raw Data'!$A$1:CN,
if($I$9<>""," 
SELECT Avg(BY),Avg(BZ),Avg(CA)
WHERE BO contains 'Closed' 
 and BD >= date """&TEXT($F$9,"yyyy-MM-dd")&""" 
 and BD <= date """&TEXT($F$11,"yyyy-MM-dd")&"""
 and AF = '"&$I$9&"'
LABEL Avg(BY),Avg(BZ),Avg(CA) 'Avg Days to Close' ","
SELECT Avg(BY),Avg(BZ),Avg(CA)
WHERE BO contains 'Closed' 
 and BD >= date """&TEXT($F$9,"yyyy-MM-dd")&""" 
 and BD <= date """&TEXT($F$11,"yyyy-MM-dd")&"""
LABEL Avg(BY),Avg(BZ),Avg(CA) 'Avg Days to Close' "))

JohnA JohnA · Accepted Answer · 2021-06-16T14:45:19

To use the aggregate functions you need to use the GROUP BY clause. The sheet you shared is not set to View access, so I am flying somewhat blind here.

=Query('BNTouch Raw Data'!$A$1:CN,
if($I$9<>""," 
SELECT Avg(BY),Avg(BZ),Avg(CA)
WHERE BO contains 'Closed' 
 and BD >= date """&TEXT($F$9,"yyyy-MM-dd")&""" 
 and BD <= date """&TEXT($F$11,"yyyy-MM-dd")&"""
 and AF = '"&$I$9&"'
GROUP BY <column1>,<column2>, etc.
LABEL Avg(BY),Avg(BZ),Avg(CA) 'Avg Days to Close' ","
SELECT Avg(BY),Avg(BZ),Avg(CA)
WHERE BO contains 'Closed' 
 and BD >= date """&TEXT($F$9,"yyyy-MM-dd")&""" 
 and BD <= date """&TEXT($F$11,"yyyy-MM-dd")&"""
LABEL Avg(BY),Avg(BZ),Avg(CA) 'Avg Days to Close' "))

https://developers.google.com/chart/interactive/docs/querylanguage#aggregation_functions

How to average multiple columns within a query?

1 Answers