Query to Google Sheets fails if ORDER BY used

[Greg Sommerville]

So, I've got this query that I'm passing to Google Sheets:

            query = 'select A, SUM(C) GROUP BY A PIVOT B';

And that works perfectly, giving me a nice stacked bar chart.  When I change it to this:

            query = 'select A, SUM(C) GROUP BY A PIVOT B ORDER BY SUM(C) DESC';

it fails, and doesn't return any data (or an error, for that matter).

I've tried moving the ORDER BY into different positions, and the result is the same.  Even if I leave off the "DESC" part, it fails.  So, the bottom line is, without sorting = works great.  With sorting = doesn't return any data and times out after a few seconds.

Is this a known bug, or am I doing something wrong?

Thanks!

[Jean-Rémi Delteil]

Hello Greg, 

According to the GViz documentation on Query language, PIVOT function: 

Columns specified in the pivot clause may not appear in the select, group by or order by clauses. When pivotis used, the order by clause cannot contain any aggregation columns. The reason for that is that for each aggregation specified in the select clause, many columns are generated in the result table. However, you can format aggregation columns when pivot is used. The result of such a format is that all of the new columns relevant to the specific aggregation, that are generated by the pivot operation, are formatted by the specified pattern. In the example above, adding format sum(salary) "some_format_string" will affect the following columns: Eng sum-salary, Marketing sum-salary and Sales sum-salary.

So, by design, your query is not valid.

[Greg Sommerville]

Very helpful, thank you.  I was able to work around this by adding a new column to the data table, populating the column with the sum of the other columns, sorting by that new column, then removing the column.