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, 2022 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, 2022 at 6:04
  • That worked but the 1st instance of WHERER has a typo. Thank you David!
    – Mr. B
    Jun 11, 2022 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, 2022 at 5:22

Your Answer

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

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