How to group by a formatted string field?

Question

I have an issue that want your help . Following is my issue : I have a table

id     | total 
1_2    |  12
1_2_3  |  13
1_2    |  14
1_2_5  |  14
1_1_1  |  14

Normal group by id will result

SELECT id , sum(total)                        id    | total 
FROM mybucket                    ->           1_2   | 26
GROUP BY id                                   1_2_3 | 13
                                              1_2_5 | 14
                                              1_1_1 | 14

But i want group by id that format field id . Example i split 1_2_3 to 1_2 and 1_2_4 to 1_2 And final result

    id    | total 
    1_2   | 26 +13+14 = 43 
    1_1_1 | 14

How can I group by such method?

Answer 1

Assume you want group until second underscore. You get two groups 1_2, 1_1 (NOT 1_1_1)

SELECT nid AS id, SUM(total) AS total
FROM mybucket
LET nid = CASE WHEN ARRAY_LENGTH(SPLIT(id, "_")) > 2 THEN CONCAT(SPLIT(id, "_")[0],"_",SPLIT(id, "_")[1]) ELSE id END;
GROUP BY nid ;