0

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?

1 Answer 1

0

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 
) )
2
  • Thanks - given the size of my spreadsheet, I am really avoiding arrayformulas as they make calculations really slow
    – Oren
    Oct 7, 2022 at 16:23
  • I do not think there will be any measurable performance difference here. To improve spreadsheet performance, see these optimization tips. Oct 7, 2022 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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