google电子表格查询计数()

Question

我可以使用下面的公式返回数据：

=ArrayFormula(Query(importRange("SPREADSHEET_KEY", "Sheet1!A1:z2500"), "Select Col8, COUNT(Col8), SUM(Col24)/COUNT(Col8) WHERE Col8 is not null GROUP BY Col8 order by SUM(Col24)/COUNT(Col8) desc))

这将返回所有可能的行。我想要实现的是，只有当COUNT(Col8) >= 5

我试过了：

=ArrayFormula(Query(importRange("SPREADSHEET_KEY", "Sheet1!A1:z2500"), "Select Col8, COUNT(Col8), SUM(Col24)/COUNT(Col8) WHERE Col8 is not null and COUNT(Col8) >=5 GROUP BY Col8 order by SUM(Col24)/COUNT(Col8) desc))

以及

=ArrayFormula(Query(importRange("SPREADSHEET_KEY", "Sheet1!A1:z2500"), "Select Col8, COUNT(Col8), SUM(Col24)/COUNT(Col8) WHERE Col8 is not null GROUP BY Col8 order by SUM(Col24)/COUNT(Col8) desc"), "SELECT * WHERE Col2 >= 5")

两个都返回了错误。

有什么想法吗？

Answer 1

你的语法在第二个公式中是错误的。将第一个查询包含在第二个查询中：

          =ArrayFormula(QUERY(Query(importRange("SPREADSHEET_KEY", "Sheet1!A1:z2500"), "Select Col8, COUNT(Col8), SUM(Col24)/COUNT(Col8) WHERE Col8 is not null GROUP BY Col8 order by SUM(Col24)/COUNT(Col8) desc"), "SELECT * WHERE Col2 >= 5"))