Google-sheets – Calculate Percentage with QUERY on Google Sheets

google sheetsgoogle-sheets-query

I have a Google Sheets database like this:

   Name  | Value  | Answer
     A   |  100   |   Y
     B   |  200   |   N
     C   |  10    |   Y
     A   |  100   |   N
     A   |  1000  |   Y
     B   |  50    |   Y
     B   |  200   |   N

And I want, by using QUERY, to get the total percentage of "Answer" like this:

Answer | %Total 
  Y    |  57%  
  N    |  42%

And the percentage of Y and N per name:

Name | Sum  | Count |  %Y  | %N  
A    | 1200 |   3   | 28%  | 71% 
B    |  450 |   3   | 14%  | 85%    
C    |  10  |   1   | 14%  | 85%

Here's what I have so far:

=QUERY(Data!A1:C8;"SELECT A, SUM(B), COUNT(B) group by A")

I got the SUM and the COUNT, but couldn't figure how to get the percentage as cited above.
Could somebody with more experience please help with that?

Best Answer

This could be easily achieved with a sub query in SQL but it seems that Google Sheets doesn't support it. Instead, you can add a calculated number of total records in the query.

=QUERY(D:D, "SELECT D, COUNT(D)/"&COUNTA(D:D)-1&" WHERE D IS NOT NULL GROUP BY D", 1)
Related Topic