Were you expecting random() to return the same sequence when the view materialized again in the subquery? Your ultimate query works fine when the random view is materialized once into a table.
CREATE TABLE v_random_hierarchy AS WITH r(parent, child) as (select null, 1 union all select abs(random())%child+1, child+1 from r) SELECT * FROM r LIMIT 5; select v1.parent, v1.child, (select count(child) from v_random_hierarchy as v2 where v2.parent IS v1.parent) as number_children_of_parent from v_random_hierarchy as v1; parent,child,number_children_of_parent ,1,1 1,2,3 1,3,3 3,4,1 1,5,3 FYI. as demonstrated above, to also count the NULL parent, use "IS" instead of "=". Some style suggestions. Uppercase SQL keywords, linebreaks, and indentation will make your SQL far more readable and easier to see what's wrong. SELECT <long-column-def> <long-column-def>, ... FROM <table-or-subquery> <long-table-or-subquery> ... WHERE <long-condition> <long-condition> Peter On Thu, Jan 18, 2018 at 2:18 PM, Shane Dev <devshan...@gmail.com> wrote: > Hello, > > Here is a view which assigns randomly chosen parents to a sequence of > children - > > CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1 > union all select abs(random())%child+1, child+1 from r) select * from r > limit 5; > > sqlite> select * from v_random_hierarchy; > parent child > 1 > 1 2 > 1 3 > 1 4 > 4 5 > > Can a column be added to this view which counts the number of children for > each parent? > > Here is my failed attempt - > > sqlite> select v1.parent, v1.child, (select count(*) from > v_random_hierarchy as v2 where v2.parent=v1.parent) as > number_children_of_parent from v_random_hierarchy as v1; > parent child number_children_of_parent > 1 0 > 1 2 3 > 2 3 0 > 3 4 0 > 3 5 1 > > in this case, it should be - > > parent child number_children_of_parent > 1 1 > 1 2 1 > 2 3 1 > 3 4 2 > 3 5 2 > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
