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)

  1. (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.

1 answer

  • answered 2019-05-15 06:14 Marcus Vinicius Pompeu

    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