I was fooling around, learning subqueries, group by and everything
unearthing my mental unknowns of SQL world. So I thought I would
divide total letters of the entire bible by total numbers of books,
chapters and verses. Here is what I came up with the best
optimization possible to my knowledge potiental, if you know of
'another' ways to do the same result, whether it reduces overhead or
not, feel free to show me your ways. The more I know, the more I
learn ;)
SELECT
T.TOT AS 'Total Length',
P.AVB AS 'Avg Length Per Book',
B.AVC AS 'Avg Length Per Chapter',
W.AVV AS 'Avg Length Per Verse'
FROM
(
SELECT
SUM(length(text))/
(
SELECT
SUM(V.verse)
FROM
(
SELECT
book,
chapter,
MAX(verse) AS 'verse'
FROM
bible
GROUP BY
book,
chapter
) AS V
) AS 'AVV'
FROM
bible
) AS W,
(
SELECT
SUM(length(text))/
(
SELECT
SUM(P.chapter)
FROM
(
SELECT
book,
MAX(chapter) AS 'chapter'
FROM
bible
GROUP BY
book
) AS P
) AS 'AVC'
FROM
bible
) AS B,
(
SELECT
SUM(LENGTH(text))/66 AS 'AVB'
FROM
bible
) AS P,
(
SELECT
SUM(LENGTH(text)) AS 'TOT'
FROM
bible
) AS T;
--
Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
