2

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?

  • I think google QUERY alone isn't capable of doing what you are trying to do. For the first percentage, you can try to use =query(A1:C8,"select C, count(C) group by C") and then apply a formula to calculate %s. – Oren Pinsky Dec 11 '18 at 21:14
  • Thanks for the reply Oren, I'm not sure if it's possible to do it using just QUERY indeed. Your idea works well, I think I'll do something this way. – T. Fontanello Dec 12 '18 at 19:47
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.