How to combine queries in Google sheets?-CodePudding

I have data that arrives as so in a sheet from a google form. It's to manage pieces of our product that we send to a painter.

in/out  model_1   model_2
-------------------------
in        10         0
out        5         0
in        10        10
in         2         5
out        2        12

I want something like so

model          IN    OUT
-------------------------
model_1        22     7
model_2        15    12

I managed to get the first column with a query with something along those lines

SELECT SUM(B), SUM(C) WHERE A="in"

Then I added a TRANSPOSE.

How to add to the query a second column where A="out" ?

This is the real query :

=TRANSPOSE(QUERY('Réponses au formulaire 2'!A1:W; "SELECT SUM(E), SUM(F), SUM(G), SUM(H), SUM(I), SUM(J), SUM(K), SUM(L), SUM(M), SUM(N), SUM(O), SUM(P), SUM(Q), SUM(R), SUM(S), SUM(T), SUM(U), SUM(V), SUM(W) WHERE D = 'DEPOT'";1))

Hope you understand what I mean. Maybe another approach is best?

But for now I'm kinda stuck but not really happy with my solution of adding a second query as it add a column of labels sum_a sum_b....

CodePudding user response：

Would this do it?

=TRANSPOSE(QUERY('Réponses au formulaire 2'!A1:W; "SELECT D,SUM(E), SUM(F), SUM(G), SUM(H), SUM(I), SUM(J), SUM(K), SUM(L), SUM(M), SUM(N), SUM(O), SUM(P), SUM(Q), SUM(R), SUM(S), SUM(T), SUM(U), SUM(V), SUM(W) WHERE D = 'DEPOT' OR D = '2nd Option' group by D";1))

Change 2nd Option with your desired value

CodePudding user response：

you could try this formula:

=transpose({{B1:G1};BYCOL(FILTER(B2:G,A2:A="in"),lambda(row,sum(row)));BYCOL(FILTER(B2:G,A2:A="out"),lambda(row,sum(row)))})