[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