Given the following example table and data: create table x(id integer);
with recursive src(id) as (select 1 union all select id + 1 from src where id < 5) insert into x(id) select id from src ; My goal is to select each row from table x, and the sum total, in the same query. The following returns the results correctly as expected: with x_all as (select id from x), x_sum as (select sum(id) from x_all) select * from x_all union all select * from x_sum ; /* id ---------- 1 2 3 4 5 15 */ However when doing an explain query plan I see that the source "x" table is scanned twice where I would have expected it to only be scanned once: selectid,order,from,detail 1,0,0,"SCAN TABLE x" 2,0,0,"SCAN TABLE x" 0,0,0,"COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)" Is this expected? For my real query the x_all data is relatively complicated with lots of joins, and I was wondering if it is absolutely necessary for SQLite to be doing double the amount of work it needs to? Mark. -- Mark Lawrence