When grouping and sum, how to get the percent distribution of the grand total in Google Sheets Query similar to its Pivot Table ‘% of grand total’?

This post describes the percent distribution of the grouped items using Query formula.

For non-grouped items, there is no requirement of Query. The same I have detailed here – Calculating the Percentage of Total in Google Sheets [How To].

Let me first make you understand how to get/calculate the percent of the grand total with the help of a Pivot Table in Google Sheets. The Query formula follows after that.

For my example, I am only taking a dataset with 5 rows including the header row.

With 4 steps you can group the data in the array B1:C5 and get the percent of grand total using Pivot Table as below.

Pivot Table Steps to Calculate Percent of Grand Total in Sheets

1. Select the array B1:C5. Needless to say, if your data range is different, select that range.
2. Click the ‘Data’ menu and select the menu item ‘Pivot table’.
3. Follow the on-screen instructions to create the Pivot Table in the ‘New sheet’ or ‘Existing sheet’.
   • I am creating the Pivot Table in the ‘Existing sheet’ cell E1. You can see the screenshot above.
   • As a side note, in order to delete the Pivot Table, click cell E1 and hit the delete button.
4. Once you have completed the above steps, the next step is to add the required fields and functions in the Pivot table editor (sidebar) panel. Once again refer to the above screenshot for the required settings which are as follows.
   • Add the field ‘Item’ against ‘Rows’.
   • Add the field ‘Amount’ against ‘Values’.
     • Under ‘Summarise by’ select the SUM function if already not, and change ‘Default’ to ‘% of grand total’ under ‘Show as’.

That’s all.

Now let’s see how to calculate the percent (distribution) of grand total in Google Sheets Query similar to the Pivot Table above.

How to Calculate the Percent of Grand Total of Grouped Items Using a Query Formula

New to Query? Then please read my Query function tutorial first.

Since our topic is on calculating the percent of grand total in Google Sheets Query grouping, it’s clear that we must use the Group by Clause in Query.

To group and sum the above data, we can use a Query formula as below.

=query({B1:C5},"Select Col1, Sum(Col2) group by Col1")

Want to see what this formula returns?

But what we want is not a group-wise sum. We want percent of grand total similar to Pivot Table as above.

Percent distribution of the grand total is actually grand_total/total_of_each_item.

For example, the percent of the grand total of the items “apple”, “mango”, and “orange” can be calculated as follows.

=250/(250+200+250)
=200/(250+200+250)
=250/(250+200+250)

In this, the above Query formula returns the individual item totals (grouped totals) which is the first part (before the division operator) of the above 3 formulas.

To get the second part, we can use the SUM function as below.

=sum(C2:C5)

We can include this SUM function within Query as below.

=query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1")

This Query formula will return the percent of the grand total of the grouped items. But it is not my final formula!

Why?

See the screenshot below.

We must add a few more additional clauses with the formula to make it include infinite rows and get a percent formatted output.

Format Number to Percent in Query Result

To format the numbers (Query output) to percent, no need to do any change to the formula.

Select the numeric data range in the result I2:I and format it to percent (Format menu, Number, Percent).

Note: The below tips are just optional. You can skip it or read on to get more idea about formatting Query result.

If you are very much particular to avoid the manual formatting, you can include the percent format within the Query. Here is the updated formula!

=query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1 format Sum(Col2)/"&sum(C2:C5)&"'0.00%'")

Related Reading: How to Format Date, Time, and Number in Google Sheets Query.

Adding Percent Grand Total Row to a Query Result

Missing a grand total percent row at the bottom of the Query result, right? Here is the formula.

={query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1 format Sum(Col2)/"&sum(C2:C5)&"'0.00%'");"Grand Total",100%}

I have just added the grand total row to the Query result using the string “Grand Total” and 100% within Curly Braces as an array.

I think we should make two more changes in the formula, but not necessary though.

See the labeling in I1. You can customize that label using the Label Clause in Query formula as below.

={query({B1:C5},"Select Col1, Sum(Col2)/"&sum(C2:C5)&" group by Col1 label Sum(Col2)/"&sum(C2:C5)&"'SUM of Amount' format Sum(Col2)/"&sum(C2:C5)&"'0.00%'");"Grand Total",100%}

Finally, make the formula to cover infinite rows.

={query({B1:C},"Select Col1, Sum(Col2)/"&sum(C2:C)&" where Col1 is not null group by Col1 label Sum(Col2)/"&sum(C2:C5)&"'SUM of Amount' format Sum(Col2)/"&sum(C2:C5)&"'0.00%'");"Grand Total",100%}

See the Where Clause before Group by.

This way you can get the percent of grand total in Google Sheets Query that involves grouping.

Thanks for the stay, enjoy!