Trying to sum a series of rows based on FK relation loops

I have a database table that contains information about stories.
I would like to be able to sum all of the word counts of stories in a series/all series to get the ‘true’ word count

My table layout’s relevant columns are StoryId, Title, Words, Prequel, and Sequel.

e.g.

12345, 'The Hobbit', 95356, NULL, 54321
54321, 'The Fellowship of the Ring', 187790, 12345, 32145
32145, 'The Two Towers', 156198, 54321, 54123
54123, 'The Return of the King', 137115, 32145, NULL
13579, 'Some other book', 1234, NULL, NULL

A series can be any number of books long and will always terminate with a NULL in the Sequel column.

I’m trying for one of two results.

1. (An ad-hoc query run on a particular story)

StoryId, Title,      Words  Prequel Sequel Total
12345, 'The Hobbit', 95356, NULL, 54321, 474582

or (I suspect this is easier and I can filter it later)

2. (A query run against the whole table)

StoryId, Title,      Words  Prequel Sequel Total
12345, 'The Hobbit', 95356, NULL, 54321, 474582
54321, 'The Fellowship of the Ring', 187790, 12345, 32145, 379226 (The sum of this and following stories)
32145, 'The Two Towers', 156198, 54321, 54123, 293313
54123, 'The Return of the King', 137115, 32145, NULL, 137115
13579, 'Some other book', 1234, NULL, NULL, 1234

I have yet to find a good way to do this with pure sql, so far I have just done the math in my head, but as the database grows that is definitely not scalable.

Answer

Please, latter on let us know the actual use you had to this solution 🙂

It’s been a very interesting and enjoyable challenge.

Here’s the solution:

WITH storyMap AS(
    SELECT
        s.storyId, s.Title, s.Words,
        NULL AS Prequel, s.Sequel, s.Words AS Total,
        convert(varchar(max), NULL) AS SeqTitles
    FROM dbo.story s
    WHERE s.Sequel IS NULL
    ----------
    UNION ALL
    ----------
    SELECT
        s.storyId, s.Title, s.Words,
        s.Prequel, s.Sequel, s.Words + sm.Total as Total,
        isnull(sm.SeqTitles + ' / ', '') + sm.Title AS SeqTitles
    FROM
        dbo.story s
            JOIN storyMap sm
            ON sm.storyId = s.Sequel
)
SELECT *
FROM storyMap sm

And the result for your sample:

storyId Title                       Words   Prequel Sequel  Total   SeqTitles
54123   The Return of the King      137115  NULL    NULL    137115  NULL
13579   Some other book             1234    NULL    NULL    1234    NULL
32145   The Two Towers              156198  54321   54123   293313  The Return of the King
54321   The Fellowship of the Ring  187790  12345   32145   481103  The Return of the King / The Two Towers
12345   The Hobbit                  95356   NULL    54321   576459  The Return of the King / The Two Towers / The Fellowship of the Ring

EDIT

My first given solution aggregated backwards from the last volume:

WITH storyMap AS(
    SELECT
        s.storyId, s.Title, s.Words,
        NULL AS Prequel, s.Sequel, s.Words AS Total,
        convert(varchar(max), NULL) AS SeqTitles
    FROM dbo.story s
    WHERE s.Prequel IS NULL
    ----------
    UNION ALL
    ----------
    SELECT
        s.storyId, s.Title, s.Words,
        s.Prequel, s.Sequel, s.Words + sm.Total as Total,
        isnull(sm.SeqTitles + ' / ', '') + sm.Title AS SeqTitles
    FROM
        dbo.story s
            JOIN storyMap sm
            ON sm.storyId = s.Prequel
)
SELECT *
FROM storyMap sm

For your sample, this query results in

storyId Title                       Words   Prequel Sequel  Total   SeqTitles
12345   The Hobbit                  95356   NULL    54321   95356   NULL
13579   Some other book             1234    NULL    NULL    1234    NULL
54321   The Fellowship of the Ring  187790  12345   32145   283146  The Hobbit
32145   The Two Towers              156198  54321   54123   439344  The Hobbit / The Fellowship of the Ring
54123   The Return of the King      137115  32145   NULL    576459  The Hobbit / The Fellowship of the Ring / The Two Towers