Google表格按SUM查询的顺序 [英] Google sheets query order by SUM

问题描述

我正在尝试执行以下查询:

I am trying to do this query:

=query(indirect($B$1), "select D, E, F, SUM(H) group by D,E,F pivot G order by SUM(H)",1)

看起来不错，但会导致错误:

It looks OK, but results in an error:

无法解析功能QUERY参数2的查询字符串:NO_AGG_IN_ORDER_WHEN_PIVOT:H如果我尝试

Unable to parse query string for Function QUERY parameter 2: NO_AGG_IN_ORDER_WHEN_PIVOT: H If I try

=query(indirect($B$1), "select D, E, F, SUM(H) group by D,E,F pivot G order by H",1) the I get: 

无法解析功能QUERY参数2的查询字符串:COL_IN_ORDER_MUST_BE_IN_SELECT: H

=query(indirect($B$1), "select D, E, F, SUM(H) group by D,E,F pivot G order by D",1) works OK.  

1. 如何按SUM(H)订购?
2. 有什么想法如何按SUM(H)来订购G的特定值(例如在Google表格的数据透视表中)?

推荐答案

您不能同时使用 ORDER BY 和 PIVOT .但是您可以使用2个查询嵌套函数.第一个使用 PIVOT ，第二个使用 ORDER BY .

You can't use ORDER BY and PIVOT at the same time. But you can use 2 query nested functions. In the first one using PIVOT and the second one with the ORDER BY.

请记住，在第二个查询中，您必须使用de Col1，Col2，...表示法:

Remmember that in the second query you must use de Col1, Col2, ... notation:

=query(query(indirect($B$1)
             , "select D, E, F, SUM(H) 
                group by D, E, F 
                pivot G"
             , 1)
       , "select *
          ORDER BY Col4"
       , 1) 

这篇关于Google表格按SUM查询的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！