How to sort pivot data in descending order in Google Sheets (Part 2)

In Part 1, we saw that Pivot table was the classic spreadsheet way of aggregating and displaying numbers from many rows and columns of data.
We also saw that sorting the way we wanted is not always possible. Using the query() function is one way of doing that and it opens up many other possibilities.


2. the Query() function

The Query() function can easily fix our sorting problem, but it requires some basic knowledge of SQL, a special language used to extract and order data from databases. Fortunately, the syntax used in Spreadsheets is simplified :

The Query() is composed of 3 parameters : =query(source of data, the query itself, the column headers if required). Here is an example and its result below :

=query(facts!A1:D50,"SELECT D, sum(C) group by D order by sum(C) desc",1)

This query gets two columns, Region (column B) and the sum of Population (column C) from the sheet called "facts". It then orders the table by decreasing order of Populations (column C) and we are done !

• facts!A1:C50 : first, we define the range of data in sheet "facts" that we need to work with. 
• SELECT D, sum(C) : select column D (region) and C (population) and sum up values from column C
• group by D : whenever there's a sum, it is necessary to indicate which field it is the sum of. In that case, we are summing up regions in column D
• order by sum(C) desc : without further indication the result will be sorted by the first column in the table, again in alphabetical order. Here, we are telling the spreadsheet to order/sort by the sum of populations (C) in decreasing order (descending order). 
• quotation marks : they are really important, you will get an error message if one of them is missing

.....

Once you know the query syntax, this method is much more flexible than using pivot tables. Here is another example. Suppose we want to display not all countries, but only countries in Asia Pacific. The Report Editor has filters but again, we want numbers to be ordered by decreasing order, so we need to add a filter in our query. This is done with a where parameter (or clause in SQL lingo).

• facts!B1:D50 : we define the range of data from the "facts" sheet. 
• SELECT B, C, D : we select columns B (Country), C (Population) and D (Region). Column D is purely optional, we will show it in the result table only to make sure we have the right data.
• where D='ASIA PAC': the where clause restricts the data that we get. In this case we only want rows from the "facts" sheet which contain 'ASIA PAC'. 
• order by C desc : we order the table by decreasing/descending order of Population (column C).
• The query must be included within double quotation marks " " and the where parameter must have its text surrounded by single quotation marks ' '.
• The 1 is the optional parameter that defines whether to include the column header from the source table (1) or not (0). 

Here is the final query :

=query(facts!A1:D50,"SELECT B,C,D where D='ASIA PAC' order by C desc",1)

.....

Sample data for this exercise can be found on this sample spreadsheet and you can find the full reference to the Query language syntax here.

Happy spreading !