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

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/%';

CodePudding user response：

This should give you your expected results. Remember when working with additional categories like ['IE/g'] you will need to add last letters to the conditional statement in where clause. I used val instead of value as a column name because in many DBMS it's a restricted keyword.

Code:

select 'IE/' || substr(col1,4,1) col, sum(val) my_sum from t1 where col1 LIKE '%/%' and col1 not like 'IE/%' and substr(col1,4,1) in ('a', 'b', 'c') group by substr(col1,4,1);

Output:

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

CodePudding user response：

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.