Skip to content
Advertisement

SQLite Sum over children in self-referencing table

I have a ecosystems table, which is self-referencing because each ecosystem can have sub-ecosystems. Each ecosystem can also have a score (which represents how healthy the ecosystem is). The columns are (with example data):

The full_slug column represents the full path from top-level ecosystem down. It is redundant as it can be deduced from the slug and parent_slug columns, but it’s there.

What I’m trying to achieve is to create a query with the same number of lines, but with a column total_score which calculate each ecosystem’s score PLUS all its children ecosystems’ score, recursively. Namely, the output should be:

I started the following query:

But unfortunately it only shows top-level ecosystems and their total score.

Advertisement

Answer

I can think of several answers…

The general answer is to use recursion…

Another option is to make use of the full_slug in a JOIN, though that will prohibit use of indexes and can often be significantly slower than the general solution above.

A third way would be to unnest/explode the full_slug (that is to create one row for each component of the full_slug), and then group by the components. SQLite doesn’t have that functionality natively, so that too probably gets solved with recursion.

Demo of all three approaches:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement