How to get sums by group, and output only those, where sum is not equal to 0?

Question

In Google Sheets, I have the following data:

    |  A  |  B  
  1 | foo |  20
  2 | bar |  50
  3 | baz | -40
  5 | bar | -50
  4 | foo | 100
  6 | baz |  60

Currenly, I'm using this query formula: =QUERY(index(A2:B1000);"select A, sum(B) where A is not null and B is not null group by A order by sum(B) desc";)

But it outputs values, where the sum of B is equal to 0.

So desired output would be:

    |  A  |  B  
  1 | foo | 120
  2 | baz |  20

How can I achieve that?

Also, is there a better way than A2:B1000 for selecting the whole range (except the first row)?

Answer 1

Main point

You can't filter by the sum within the query that forms that sum. But you can run another query on the output of the first one. Like this:

=query(query(....), "select * where Col2 <> 0")

Other remarks

• I don't see the need for index command here.
• Instead of A2:B1000, use A2:B to capture all rows to the bottom of the sheet.
• It is better to include the header row in the queried range, designating it as a header with the third parameter (number of queried rows). That is, query(A1:B, "select...", 1) is preferable to query(A2:B, "select..."). When you don't provide the third parameter, the command guesses whether the top row is header or data; and it will sometimes guess wrong.
• I don't think you need "and B is not null", as null values will be ignored by the sum anyway.

So, my version of the command is

=query(query(A1:B, "select A, sum(B) where A is not null group by A order by sum(B) desc", 1), "select * where Col2 <> 0", 1)