How to use substring and charindex in google query language

I have a google spreadsheet sheet with several columns:

A: date B: string C: number … G: string (could be empty) H: string (could be empty)

I would like to have a small table with the following:

Get the sum of C in table, where rows are the values of G (substring of it, as they are configured as this: CATEGORY:ITEM, I need grouping by CATEGORY only) and columns are monts

So far I’ve got only partial solutions with query (for example group by month(toDate(A)) etc) – I seem to be unable to use substring and charindex nor left to do string manipulation to remove the item part after the category nor to visualize those as the first clumns in the resulting rows…

Edit: just to clear up a bit: I need to alter the value in G for each row so that I can group by the altered value. I know it is possible to do with dates ( in my example -> group by month(toDate(A)) gives me access to each value in A column so the result is grouped correctly for each separate month). But it seems string manipulation is not allowed?

How do I do that for starters.

Thanks

Answer

Based on your desired output, can you try this on sheet 2:

=ArrayFormula(query({day(Sheet1!A2:A10)&text(month(Sheet1!A2:A10), " (mmm)"), Sheet1!B2:F10, regexextract(Sheet1!G2:G10, "(.+):")}, "select Col7, sum(Col3) group by Col7 pivot Col1"))

and see if this is getting somewhere ?

Or in case you prefer open ended ranges:

=ArrayFormula(query({day(Sheet1!A2:A)&text(month(Sheet1!A2:A), " (mmm)"), Sheet1!B2:F, iferror(regexextract(Sheet1!G2:G, "(.+):"))}, "select Col7, sum(Col3) where Col7 <>'' group by Col7 pivot Col1"))