2

In my table, in Google Sheets, I have a column B called "description" and I'm trying to write a SQL query to group by substrings of column B. Values in B are like "Sell 1 Jan11 300.0/307.5 Strangle" and I just want to group by 'Jan11'. I have a few rows with Jan11 then it switches to Jan18, etc.

I've tried substring, char index, mid, and nothing is working I've tried:

=QUERY('spgsh1378'!A1:AP,"select B, mid(B,7,5), sum(M) group by mid(B,7,5)"

=QUERY('spgsh1378'!A1:AP,"select B, substring(B,7,5), sum(M) group by substring(B,7,5)")

=QUERY('spgsh1378'!A1:AP,"select B, substring(B, 2, CHARINDEX(' ', B) - 2), sum(M) group by substring(B, 2, CHARINDEX(' ', B) - 2)"

I've gotten a parse error that says

"Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 14. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "" ... "+" ... "-" ... "/" ... "%" ... "" ... "/" ... "%" ... "+" ... "-" ..."

player0
  • 124,011
  • 12
  • 67
  • 124
ajg
  • 59
  • 1
  • 6

1 Answers1

2

your tries are not valid syntaxes. try like this:

=ARRAYFORMULA(QUERY({MID(spgsh1378!B1:B, 7, 5), spgsh1378!A1:AP}, 
 "select Col3,Col1,sum(Col14) 
  where Col1 is not null 
  group by Col1,Col3
  label sum(Col14)''", 0))

=ARRAYFORMULA(QUERY({QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO}, 
 "select Col1,sum(Col3) 
  where Col1 is not null 
  group by Col1
  label Col1'month',sum(Col3)'net'",1), 
 IF(LEN(QUERY(QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO}, 
 "select Col1,sum(Col3) 
  where Col1 is not null 
  group by Col1
  label Col1'month',sum(Col3)'net'",1),"select Col1",0)), {"";MONTH(LEFT(
 QUERY(QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO}, 
 "select Col1,sum(Col3) 
  where Col1 is not null 
  group by Col1
  label Col1'month',sum(Col3)'net'",1),"select Col1 offset 1", 0), 3)&1)&RIGHT(
 QUERY(QUERY({MID('grouping project'!A1:A, 8, 5), 'grouping project'!A1:AO}, 
 "select Col1,sum(Col3) 
  where Col1 is not null 
  group by Col1
  label Col1'month',sum(Col3)'net'",1),"select Col1 offset 1", 0), 2)}, )*1}, 
 "select Col1,Col2 order by Col3"))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I used this: = ARRAYFORMULA(QUERY({MID('spgsh1378'!B1:B, 8, 5), 'spgsh1378'!A1:AP}, "select Col1,sum(Col14) where Col1 is not null group by Col1 label sum(Col14)'net'")) and it worked except the "mid" function was used on the header so "description" is now "tion". Also, Feb01 is before Jan11, Jan25, etc. How do I change that? – ajg Aug 05 '19 at 20:17
  • sure, what's the easiest way to share it with you? – ajg Aug 05 '19 at 20:34
  • just paste a link here and make sure the sheet is not private – player0 Aug 05 '19 at 20:47