0

I have a query() that outputs a table with two columns (A and B) of numbers. Pictured in IMAGE #1.

=QUERY(myData!A:G, "Select E,F WHERE D ='Books'")

IMAGE #1

enter image description here

But I need a function that outputs only three values. Pictured in the green row of IMAGE #2. The number of rows is variable.

  1. Sum of column A
  2. Sum of column B
  3. Sum of column C (column C is the product of A*B)

Thanks for any help you can provide!

IMAGE #2

enter image description here

1 Answer 1

1
={
QUERY(myData!A:G,"SELECT SUM(E), SUM(F) WHERE D = 'Books'"),
{"product";SUM(QUERY(myData!A:G,"SELECT E*F WHERE D = 'Books'"))}
}

This is simply uses aggregation functions.

4
  • Thank you for your help. The =QUERY(myData!A:G, "Select SUM(E), SUM(F) WHERE D ='Books'") portion of the function works but the SUM(E*F) portion throws this error. Error Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "*" "* "" at line 1, column 14. Was expecting: ")" ... Do you know how to correct that? Thanks again!
    – Mr. B
    Jun 9 at 3:42
  • it seems that aggregation functions don't work on modified columns. Nevertheless, here is a fixed formula. It queries for data, then just use the native SUM function to add it all up instead of using the QUERY's sum function. Thank you for pointing that out!
    – David Tan
    Jun 9 at 6:04
  • That worked but the 1st instance of WHERER has a typo. Thank you David!
    – Mr. B
    Jun 11 at 4:42
  • Thank you Mr. B for correcting my typo! If you would, please do accept my answer by hitting the checkmark below to show that this in fact did help you. (and give me a bit more reputation.) Much appreciated!
    – David Tan
    Jun 11 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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