IF statement in query

Question

I have a table with two columns A and B the first is a tag and the second is an amount. I am trying to write a query with two columns, one summing up negative values while the other summing up positive ones.

Coming from SQL, I tried the following

=QUERY(A1:B100, 
      "SELECT A, SUM( B * IF(B>0, 0, 1) ), 
                 SUM( B * IF(B<0, 0, 1) ) GROUP BY A ")

But it seems that the IF function is not supported in a query. I know I can create two intermediate columns in my sheet (one for positive value and one for negative ones), but I was wondering if it's possible to achieve what I want with a query or somehow without intermediate columns.

Answer 1

If you must use the query function, assuming your Tag Data is in Column A, and your Values in Column B:

=arrayformula(query({A1:A100,if(B1:B100>0,B1:B100,),if(B1:B100<0,B1:B100,)},"Select Col1, sum(Col2), sum(Col3) where Col1 <>'' group by Col1 label Col1 'Tag', sum(Col2) 'Positive', sum(Col3) 'Negative'"))

Here's the example output: https://docs.google.com/spreadsheets/d/1DW5CyPCC71CopW48uKy6basn-WP4hMfh7kuuJXT-C4o/edit#gid=1606239479

Answer 2

=arrayformula(query({a1:a100,if(b1:b100>0,b1:b100,),if(b1:b100<0,b1:b100,)},"Select Col1,sum(Col2),sum(Col3) group by Col1"))

Answer 3

Please see this sheet for an example of using the FILTER function which is probably better than your query function for this use case: https://docs.google.com/spreadsheets/d/1DW5CyPCC71CopW48uKy6basn-WP4hMfh7kuuJXT-C4o/edit?usp=sharing

I didn't know what you meant by tag, but I just created a list of random words, 10 negative, and 10 positive

With Tags in Column A and Numbers in Column B. Then in Column D I put this for the "positive" filter:

=filter($A$2:$A,$B$2:$B>0)

And for the Positive Sum:

=sum(filter($B$3:$B,$B$3:$B>0))

And in Column E for the Negative filter:

=filter($A$2:$A,$B$2:$B<0)

And for the Negative Sum:

=sum(filter($B$3:$B,$B$3:$B<0))

EDIT: I added another sheet in the workbook that shows you how to list the sum next to each tag in a filtered list of the tags: On this sheet, I created examples of how to list the total sums of each particular tag: https://docs.google.com/spreadsheets/d/1DW5CyPCC71CopW48uKy6basn-WP4hMfh7kuuJXT-C4o/edit#gid=1784614303

This formula will look at the list of tags/values in Columns A & B, and then match and sum all tags that are in the cell to the left in Column D:

=sum(filter($B$3:$B,$B$3:$B>0,$A$3:$A=D3))