Unable to parse query string for Function QUERY parameter 2: ADD_COL_TO_GROUP_BY_OR_AGG: Col2

Question

I have two query formulas, with one inside the other (This is the way my manager figured it out and wants to proceed using this methodology for some reason). What's happening in the initial query formula is that four columns are selected from a specific google sheet. This creates a new table of data that the second query will use. Before the second query executes, one of the columns of the table is an average of a column from the initial data set. From the new table that is created, a sum of the second column is created and then grouped by the fourth column. My formula looks like this:

=QUERY(QUERY({Data!A:Z},"select Col2, Col15, AVG(Col16), Col22 where Col2 ='USTIW'"),"select Col4, Sum(Col2) group by Col4")

It worked initially when AVG(Col16) was not in the formula, but now it does not work. I then get an error that is returned that is the same as my questions header. More specifically, my question is: Is it not possible to have an average function on the inner-most query formula and then have a sum function on the outer-most query formula?

Here is some dummy data for a minimal reproducible example

Answer 1

To get both SUM and AVG to appear in the resulting data array, the formula should be changed to:

=QUERY(QUERY({Data!A:Z},"select Col2, Col15, Col16, Col22 where Col2 ='USTIW'"),"select Col4, SUM(Col2), AVG(Col3) group by Col4")