Google Sheets Query - Merging 2 queries or getting a new grantotal column for pivot result-CodePudding

Image Screenshot

Hi all!
As you can see in the picture (Table-Desired result) I am trying to get a subtotal as column for a query from the table (Main Data Source). I have tried to merge 2 different queries in 1 cell formula without any success.
Below I show you how I wrote the formula separatedly:

1st

=QUERY(IMPORTRANGE("source";"'Sheet'!A:D");"select Col1, sum(Col2), sum(Col4) group by Col1")

2nd

=QUERY(IMPORTRANGE("source";"'Foglio24'!A:D");"select Col1, sum(Col4) group by Col1 pivot Col3")

OR you have any suggestions by not doing 2 query merge?

Thank you!

CodePudding user response：

Try below formula-

={{"Date";BYROW(UNIQUE(A3:A11),LAMBDA(x,x))},
{"Sum Qty";BYROW(UNIQUE(A3:A11),LAMBDA(x,SUMIFS(B3:B11,A3:A11,x)))},
{"New";BYROW(UNIQUE(A3:A11),LAMBDA(x,SUMIFS(D3:D11,A3:A11,x,C3:C11,"New")))},
{"Old";BYROW(UNIQUE(A3:A11),LAMBDA(x,SUMIFS(D3:D11,A3:A11,x,C3:C11,"Old")))},
{"Sub Total";BYROW(UNIQUE(A3:A11),LAMBDA(x,SUMIFS(D3:D11,A3:A11,x)))}}

CodePudding user response：

You can simply join them side by side using array literals {\} and lookup only the needed columns with HLOOKUP

=ARRAYFORMULA(
  LAMBDA(rg;
    HLOOKUP(
      {1\2\5\6\3};
      {
        SEQUENCE(1;6);
          {
            QUERY(rg;"select Col1, sum(Col2), sum(Col4) group by Col1";0)\
            QUERY(rg;"select Col1, sum(Col4) group by Col1 pivot Col3";0)
          }
      };
      SEQUENCE(ROWS(rg);1;2);
      0
    )
  )(QUERY(
    IMPORTRANGE("source";"'Sheet'!A:D");
    "where Col1 is not null";0)
  )
)

Add IFERROR(), if needed.