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.
On Monday, May 23, 2016 at 4:26:17 AM UTC-4, Jean-Rémi Delteil wrote: > Hello Greg, > > According to the GViz documentation on Query language, PIVOT function > <https://developers.google.com/chart/interactive/docs/querylanguage#pivot> > : > > 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. > > > > On Monday, 23 May 2016 00:43:14 UTC+2, Greg Sommerville wrote: >> >> 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! >> >> -- You received this message because you are subscribed to the Google Groups "Google Visualization API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/google-visualization-api. To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/76efb8ba-15d3-44c2-841c-531802166050%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
