Is there an alternative to a nested query call in Google Sheets?

Question

I've written these queries but I think I might have found a bug in Google Sheets:

=IFERROR(QUERY(QUERY({Sheet1!$A$2:$E},"select Col2, sum(Col4) where Col5 is null group by Col2 label sum(Col4) ''"), "Select Col1"))

The other one is the same, with a different column:

=IFERROR(QUERY(QUERY({Sheet1!$A$2:$E},"select sum(Col4) where Col5 is null group by Col2 label sum(Col4) ''"), "Select Col1"))

Removing the X's in column E shows the query data in F:G, but deleting the X's keeps one of the values. Here is a live example.

The bug goes away if I move the columns around or if I delete the G column or if I delete the label hack.

Unfortunately, although this example appears contrived, it comes from a more complex spreadsheet that I can't modify so easily, so I'm stuck with the column/row arrangement.

Assuming this is a bug that the Google Sheets team should fix (probably caused by the label sum(Col4) '' hack), is there any other way to do this?

• Given a table with multiple products, quantities and invoice states
  • get the rows that have not been invoiced (this may be 0 because they're all invoiced or it might be 0 because there are no products, hence the IFERROR call and the label sum(Col4) '' hack)
    • put the products array (from the filtered rows) in a specific cell (hence the second query call)
    • do the same with the quantities

Edit after MattKing's and player0's suggestions

Here is the expected state when nothing is invoiced (see live example)

However, this is what happens (some of the time, since I've noticed that the bug comes and goes) when I make all items invoiced:

Notice that the value in cell F7 is a literal 'p3' leftover from the QUERY. This shouldn't be there, which makes me think it's a Google Sheets bug

Answer 1

try:

=IFERROR(QUERY(QUERY({Sheet1!A2:E},
 "select Col2,sum(Col4) where Col5 is not null group by Col2"), 
 "offset 1", 0))

update:

the empty row on top is a residue of including blank rows. to counter that you can use:

=IFERROR(QUERY(QUERY({Sheet1!$A$2:$E},
 "select Col2, sum(Col4) where Col5 is null and Col4 is not null group by Col2"), 
 "select Col1 offset 1", 0))

or even:

=IFERROR(QUERY(QUERY({Sheet1!$A$2:$E},
 "select Col2, sum(Col4) where Col5 is null and Col4 is not null group by Col2 label sum(Col4)''"), 
 "select Col1", 0))

or shorter:

=IFNA(INDEX(QUERY({Sheet1!A$2:E},
 "select Col2,sum(Col4) where Col5 is null and Col4 is not null group by Col2 label sum(Col4)''"),,1))

---

=IFNA(QUERY({Sheet1!A$2:E},
 "select sum(Col4) where Col5 is null and Col4 is not null group by Col2 label sum(Col4)''"))