Google Sheets: Use Custom Headers For Grouped QUERY()

user_190925 0
Viewed 102

I've added a grouped QUERY() which groups the Stocks in an ETF by Sector, for use in a Chart, which works fine. However I'd like the resultant range to use custom headers, and use a descending order.

The data:

enter image description here

The query result:

enter image description here

First I tried to add custom headers using "AS" but this failed:

=QUERY(F9:I114,"select F as 'Sector', sum(I) as 'Weight', count(I) as 'Stocks' group by F")

Then I tried to ORDER the result, no luck either:

=QUERY(F9:I114,"select F, sum(I), count(I) group by F order F desc")

[edit] Correction, I found that "ORDER" should be "ORDER BY" which works with col F, but I actually want to order by the SUM(I), so successfully used:

=QUERY(F9:I114,"select F, sum(I), count(I) group by F order by sum(I) desc")

My SQL is quite rusty, and I haven't found any relevant examples, so any suggestions would be appreciated. MTIA

google-sheets sql
1 Answers

Good job, you are pretty close. QUERY() is based on SQL but like all implementations have its own nuances and QUERY() is not a full implementation. You can change the headers using the LABEL clause. You are correct regarding the ORDER BY.

Change this:

=QUERY(F9:I114,"select F as 'Sector', sum(I) as 'Weight', count(I) as 'Stocks' group by F")

to this:

=QUERY(F9:I114,"select F , sum(I), count(I) group by F LABEL F 'Sector', SUM(I) 'Weight', COUNT(I) 'Stocks' ORDER BY SUM(I) DESC")
user_89418940
Related