Extracting summary from selected records as a division of values

Question

Input table is like this:

Year	Region	ID	Value
2021	Asia	01	15
2021	Europe	02	35
2021	Asia	03	45
2022	Europe	04	49

I'm selecting entries by the year, region and id:

SELECT year,
       region,
       id,
       value
FROM my_table
WHERE region = 'Asia'
  AND year = 2021
  AND (id = 01 OR id = 03)

And the result is:

Year	Region	ID	Value
2021	Asia	01	15
2021	Asia	03	45

Then I need to divide the value in the string with ID 03 (45) by the value in the string with ID 01 (15) and get the result (3) under the selected strings, like this:

Year	Region	ID	Value
2021	Asia	01	15
2021	Asia	03	45
2021	Result		3

Here's what I tried:

SELECT year,
    region,
    (id = 03 / id = 01),
    value
FROM my_table
WHERE region = 'Asia'
    AND year = 2021
    AND (id = 01 OR id = 03)

but it doesn't work.

Is it ever possible in SQLite and what should the request be?

Answer 1

An idea could be using window functions inside a subquery, to select the first and the last values you've filtered with your subquery. Once your values are selected, you divide one over the other. Then use UNION ALL to gather the results of the subquery and the summary record.

WITH cte AS (
    SELECT Year_,
           Region,
           ID,
           Value
    FROM my_table
    WHERE Region = 'Asia'
      AND Year_ = 2021
      AND ID IN (01, 03)
)
SELECT * FROM cte
UNION ALL
SELECT DISTINCT 2021, 'Result', NULL, 
                1.0* FIRST_VALUE(Value) OVER(ORDER BY ID DESC) /
                  FIRST_VALUE(Value) OVER(ORDER BY ID)
FROM cte

Output:

Year_	Region	ID	Value
2021	Asia	1	15
2021	Asia	3	45
2021	Result	null	3

Check the demo here.