[thelist] SQL question: Will this require a sub-select?

Bill Moseley moseley at hank.org
Fri Sep 5 14:10:32 CDT 2008


Here's an example schema for tracking books and chapters:

    create table book (
        id          int,
        name        text,
        pub_date    date,
        author      int references author,  -- etc...
    );

    create table chapter (
        id          int,
        book        int references book,
        word_count  int
    );

So, every book is made up of one or more chapters and every chapter
has a word count.

I need to count books, total words, and "units" which is book count +
a unit for every 20,000 words *in that book*.

It's the unit count I'm wondering about.


So, I might want a summary by month showing total books published and
total words.

    SELECT
        date_trunc( 'month', book.pub_date ) as month,
        COUNT( DISTINCT book.id ) as books,
        SUM( chapter.word_count )
    FROM
        book JOIN chapter on chapter.book = book.id
    GROUP BY
        month

Now I need to count my units.  I have to count them per book, so it
seems I would need a sub-select.

I can't simply divide the above SUM(word_count) by 20,000 -- as that
will end up giving an incorrect count.


Something like:


    SELECT
        date_trunc( 'month', book.pub_date ) as month,
        COUNT( book.id ) as books,
        SUM( chptr.word_count ) AS total_words,
        SUM( chptr.units ) AS total_units

    FROM
        book
        JOIN (
            SELECT
                book,
                SUM(word_count) AS word_count,
                ( SUM( word_count ) / 20000 ) + 1 ) AS units
            FROM
                chapter
            GROUP BY
                book
        ) AS chptr ON chptr.book = book.id

    GROUP BY
        month

I'm not seeing any way around the sub-select.  Is there one?

I guess I could de-normalize and place the total words in the book
table.




-- 
Bill Moseley
moseley at hank.org
Sent from my iMutt




More information about the thelist mailing list