How to combine the code of two array queries in Google Sheets

Question

I need to combine two queries that are both inside arrayformulas so that I just have one query:

I've tried using Union

First Code:

 = ARRAYFORMULA(QUERY({MID(Sheet1!B1:B, 8, 5), Sheet1!A1:AS}, 
 "select count(Col13) 
 where Col13>=0 
 group by Col1 
 label count(Col13)'Winners #'"))

Second Code:

= ARRAYFORMULA(QUERY({MID(Sheet1!B1:B, 8, 5), Sheet1!A1:AS}, 
"select count(Col13) 
where Col13<=0 
group by Col1 
label count(Col13)'Losers #'"))

Welcome. Would you please share a copy of your spreadsheet (excluding any private or confidential data). — Tedinoz
– Tedinoz, Commented Aug 9, 2019 at 0:55
@Tedinoz the "grouping project" tab has the data, "Sheet 1" has the two queries I'm trying to combine, and "Sheet 2" has the dates sorted but I would also like to know how to add more selections to that query such as 'count(Col3)' but this requires adding more columns. If you know how to do that, that would be great. Thank you so so much — ajg
– ajg, Commented Aug 9, 2019 at 2:01

player0 · Accepted Answer · 2019-08-09 02:52:48Z

4

=ARRAYFORMULA(QUERY(REGEXREPLACE(TO_TEXT(QUERY({
 QUERY({MONTH(MID('grouping project'!A2:A, 8, 3)&1)&"♦"&
              MID('grouping project'!A2:A, 8, 5), 'grouping project'!A2:AO}, 
 "select Col1,count(Col3),'Winners #'
  where Col1 is not null 
    and Col3 >= 0 
  group by Col1 
  label count(Col3)'','Winners #'''", 0);
 QUERY({MONTH(MID('grouping project'!A2:A, 8, 3)&1)&"♦"&
              MID('grouping project'!A2:A, 8, 5), 'grouping project'!A2:AO}, 
 "select Col1,count(Col3),'Loosers #'
  where Col3 <= 0 
    and Col1 is not null
  group by Col1 
  label count(Col3)'','Loosers #'''", 0)}, 
 "select Col1,sum(Col2)
  group by Col1
  pivot Col3
  label Col1'Week ending'", 0)), "^.+♦", ), 
 "where Col1 is not null", 0))

answered Aug 9, 2019 at 2:52

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ajg Over a year ago

thank you SO much. I'm new to SQL...anyway you could describe the code?

player0 Over a year ago

@ajg basically, both of your queries are put under each other and to each is assigned "fake" column (either winners or losers). then its put as a whole under query again where we use fake sum so we could pivot it based on the fake win/lose column. regex is there just to fix chronology of column A

Tedinoz Over a year ago

A work of art - where does one start? In the formula for MONTH, would you explain the purpose of &1? In the second MID, what is the purpose of &"♦"& to join the MONTH value with the Mid value? In the first SELECT statement, what is the significance of 'Winners #' after the count statement? I note a label is added later in the SELECT.

player0 Over a year ago

in order to convert month names eg. Jan, Feb, etc to numbers you need: =MONTH("Jan"&1) hence the MONTH and &1. then we join it with unique symbol ♦ (could be whatever symbol ♪♫♥♣♠♂♀...) so stuff in column A would look like: 01♦Jan11, 01♦Jan18, 01♦Jan25, 02♦Feb01, 03♦Mar03, etc. this way column A can be nicely sorted chronologically and after its sorted the extra stuff is removed by regex formula "^.+♦" - which translates as "remove everything from start till unique symbol ♦ including unique symbol itself"

Tedinoz Over a year ago

@player0 Appreciate your explanations.

|

Collectives™ on Stack Overflow

How to combine the code of two array queries in Google Sheets

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related