Google Sheets | Query | Function | PIVOT | Clause | Example | GROUP BY | Spreadsheet | Tutorial

Use the Google Sheets QUERY function and PIVOT clause to create a column for each unique value in the column, in the source data range, specified for pivot. This clause allows to use the following aggregate functions: avg, count, sum, max, and min.
Let's look at an example where using the PIVOT clause would be ideal: Say there is a source data range with three columns Asset Class, No. of Investments, and Branch Code. Some of the values in the cells of Asset Class column are FD, Equity, and Equity MF. Assume you want the total number of investments in each asset class across all branch codes. The QUERY function with the PIVOT clause can return the required data.
For an intro to QUERY function, please refer to the tutorial How to Use Query in Google Sheets whose link is given below.
-------------------------------------
How to Use QUERY in Google Sheets?
QUERY helps, among others, to extract specific or all data from a range:
• Google Sheets | QUERY ...
-------------------------------------
How to Use QUERY with WHERE clause and MATCHES operator in Google Sheets?
MATCHES operator assists to conduct an exact or a wildcard search:
• Google Sheets | QUERY ...
-------------------------------------
How to Use QUERY with WHERE clause and starts with or ends with operators in Google Sheets?
Use where clause and starts with, ends with or both the operators to get data based on one or more conditions:
• Google Sheets | QUERY ...
-------------------------------------
How to Use QUERY with WHERE clause in Google Sheets?
To conditionally extract data, use the QUERY function with its WHERE CLAUSE:
• Google Sheets | QUERY ...
-------------------------------------
How to Use XLOOKUP to Extract Multiple Values in Google Sheets?
XLOOKUP can return a single row or column with the search key:
• Google Sheets | XLOOKU...
-------------------------------------
How to Use VLOOKUP in Google Sheets?
Use VLOOKUP to get a single value:
• VLOOKUP Google Sheets ...
-------------------------------------
How to Use IFS in Google Sheets?
IFS allow multiple logical expressions:
• Google Sheets IFS | Te...
-------------------------------------
How to Use IF in Google Sheets?
Use IF to compare one value with another:
• Google Sheets IF | Tut...
-------------------------------------
How to Use IF with AND in Google Sheets?
Use IF with AND to Use Multiple Logical Expressions in IF:
• Google Sheets | IF | A...
-------------------------------------
How to Create a Pivot Table in Google Sheets?
Create a pivot table for calculation and in-depth data analysis:
• Google Sheets | Pivot ...
-------------------------------------
Which one to Use? A Pivot Table, or COUNTBLANK Function, to Count Blank Cells in Google Sheets:
Gives steps to use a pivot table or COUNTBLANK to count blank cells:
• Google Sheets | Pivot ...
-------------------------------------
Key point:
The column(s) that is included in the PIVOT clause should not be included
in the select clause.
Here's the format of the QUERY function formula:
=QUERY(data, query, [headers])
Use the PIVOT clause in the query part of the formula.
Let's consider an example:
Example
Assume A2 to C362 is the source data range. Cells A1, B1, and C1 are labelled as Asset Class, No. of Investments and Branch code. Say that you want the total number of investments in each asset class across all branch codes.
The QUERY function formula with PIVOT clause is:
=QUERY(A2:C362, "select SUM(B) PIVOT A")
The above formula will return the required data, with a column for each unique value in column A.
Take a look at this video tutorial, which gives the step to use the Google Sheets QUERY function with the PIVOT clause, with examples.