How to get the current_row in a main query for a subquery?

Let assume the following table:

CREATE TABLE t1 (col1 TEXT PRIMARY KEY, value INTEGER);
INSERT INTO t1 VALUES 
    ('IE/a', 1), ('IE/b', 2), ('IE/c', 3) ,
    ('00/a', 10), ('01/a', 20), ('02/a', 30), ('03/a', 40),
    ('00/b', 100), ('01/b', 200), ('02/b', 300), ('03/b', 400),
    ('00/c', 1000), ('01/c', 2000), ('02/c', 3000), ('03/c', 4000);

The content of the table is:

col1    value
IE/a    1
IE/b    2
IE/c    3
00/a    10
01/a    20
02/a    30
03/a    40
00/b    100
01/b    200
02/b    300
03/b    400
00/c    1000
01/c    2000
02/c    3000
03/c    4000

I want to get the following output:

IE/a  100          
IE/b  1000          
IE/c  10000

So, IE/a is the sum of the values of 00/a + 01/a + 02/a + 03/a.

My first approach looks like this, where current_row_id as my pseudeo_code_variable to demonstrate that a would like to consider the current row:

SELECT
   col1
 , (SELECT sum(value) FROM t1 
     WHERE col1 = '00' || SUBSTRING( current_row_col1, 3)
        OR col1 = '01' || SUBSTRING( current_row_col1, 3)
        OR col1 = '02' || SUBSTRING( current_row_col1, 3)
        OR col1 = '03' || SUBSTRING( current_row_col1, 3)
    ) AS value
FROM t1
WHERE col1 LIKE 'IE/%';

Solution

Use a self join and aggregation:

SELECT t1.col1,
       TOTAL(t2.value) AS total
FROM tablename t1 LEFT JOIN tablename t2
ON SUBSTR(t2.col1, INSTR(t2.col1, '/') + 1) = SUBSTR(t1.col1, INSTR(t1.col1, '/') + 1)
AND t2.rowid <> t1.rowid  
WHERE t1.col1 LIKE 'IE/%'
GROUP BY t1.col1;

Or, with conditional aggregation:

SELECT MAX(CASE WHEN col1 LIKE 'IE/%' THEN col1 END) AS col1,
       TOTAL(CASE WHEN col1 NOT LIKE 'IE/%' THEN value END) AS total
FROM tablename
GROUP BY SUBSTR(col1, INSTR(col1, '/') + 1);

Or, with window functions:

SELECT DISTINCT
       MAX(CASE WHEN col1 LIKE 'IE/%' THEN col1 END) OVER (PARTITION BY SUBSTR(col1, INSTR(col1, '/') + 1)) AS col1,
       TOTAL(CASE WHEN col1 NOT LIKE 'IE/%' THEN value END) OVER (PARTITION BY SUBSTR(col1, INSTR(col1, '/') + 1)) AS total
FROM tablename;

See the demo.