Thursday, October 18, 2012

Google Spreadsheet Query Group by

Question:
 
( by Johnny Wang88 )



Hi, Google docs Pros!!

I only know how to use sum in google docs, but would like to know how to use filter or search to sum the fields that I want. Here is what I need. I have a bunch of data like below:




as could be seen above, there will be different names and numbers for each name, and this datasheet will be growing each months with new data with more names and numbers, I want to sum the numbers of each specific names, for example like the result:





Thanks so much in advance....as this is probably very easy for Pros, but I've tried 2-3 hours still can't figure it out!! need help! Thanks!!
Johnny

Solution:

Suppose if you have the data in 'Sheet1' and then if you want to have the results in 'Sheet2' then look at the following example:

Have a look at the following screenshot of 'Sheet1':



If you want the results in same sheet then use the following formula:
=query(A:B;"select A,sum(B) where A <> '' group by A label sum(B) '' ")
OR you can also use the following formula:
=query(ArrayFormula(A:B);"select Col1,sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) '' ")

And if you want the results in new Sheet then, make a new sheet, lets call it 'Sheet2'

In 'Sheet2' put the following formula in Cell A1:
=query(Sheet1!A:B;"select A,sum(B) where A <> '' group by A label sum(B) '' ")

Alternatively you can also use the following formula:
=query(ArrayFormula(Sheet1!A:B);"select Col1,sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) '' ")


The above query will fill the data as shown in the screenshot below:
Have a look at the following screenshot of 'Sheet2':





I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

8 comments:

  1. thanks for your quick reply, I think this is exactly what I want. Thank a millions!! Just want to ask what does where A <> means? I know capital A is for column A, what does <> mean in the code there?

    ReplyDelete
  2. Most Welcome.....
    Here the "A<>" means where A is not equal null ( or blank )

    equal to means =
    not equal to means <>

    I hope you understand it now.

    ReplyDelete
  3. you are so great! I understood now. Thanks a lot!! I have another question, if I have a specific column that I want to include in the query from other source of sheet, how could I do that? For example:

    The age of the name,

    Bill is 56
    Chris is 32
    Dick is 23
    John is 44
    Mary is 31

    How could I show them after the added number column? Thanks.

    ReplyDelete
    Replies
    1. If you can provide me the sample spreadsheet then may be I can help you!! :)

      Delete
  4. thanks, here is the sample spreadsheet,

    https://docs.google.com/spreadsheet/ccc?key=0AlQ8FWPdXEoTdGx5a0RlSm1jZ3ZDZ29Ram94OGg4eHc

    I want to use query from sheet 1 and sheet 2 to get result like shown in sheet 3

    thanks a millions

    ReplyDelete
  5. That's very interesting!!
    Is there any way to do the same with Strings?
    I mean, in your example they are numbers, and you do SUM... but if they weren't numbers but strings?

    something like "select A, join(", ",B) where A <> "group by A" "
    And the other hand, why "group by A" is between " ???

    Thanks so much

    ReplyDelete