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” … “,” … “” … “+” … “-” … “/” … “%” … “” … “/” … “%” … “+” … “-” …”
Advertisement
Answer
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"))