Sum two grouped columns in Google Sheets QUERY Language

google sheetsgoogle-sheets-query

I want QUERY command to provide me with the sum of two columns, and group the result by Column A.

The following is not working (parsing error)

=query(Ledger!A2:G,"SELECT A, sum(D+E) where (B = ...) group by A order by A")

… neither is this (empty results)

=query(Ledger!A2:G,"SELECT A, sum(D)+sum(E) where (B = ...) group by A order by A ")

I can, of course, use the query below, and sum the columns using the =SUM() command, but I would rather have the query already return the sum of both columns.

=query(Ledger!A2:G,"SELECT A, sum(D), sum(E) where (B = ...) group by A order by A")

based on this, and this, my query should be working, but it is not. Why? Any workaround (that does not involve array formulas or using the =SUM() function?

Best Answer

The sum clause will not work the way you expect with blank values. To convert the blanks to zeros, use n(), like this:

=arrayformula( query( 
  { Ledger!A2:C, n(D2:E) }, 
  "select Col1, sum(Col4) + sum(Col5) 
   where Col2 = 42 
   group by Col1", 
  0 
) )

The group by clause sorts the primary column automatically, so an order by clause is not needed here.

Another perhaps a simpler way to do the same is to do the math in the array, like this:

=arrayformula( query( 
  { Ledger!A2:C, D2:D + E2:E }, 
  "select Col1, sum(Col4) 
   where Col2 = 42 
   group by Col1", 
  0 
) )
Related Topic