[sqlite] Can a record count column be added to this random hierarchical view?

[Shane Dev]

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