Prev: how to explain such a regular syntax
Next: CURDATE
From: "Ron Piggott" on 13 Aug 2010 08:43
I am making a Bible concordance. I need help with the query that figures
out how many times each word is in the Bible.

The COUNT() in the query below is giving me the total number of words for
the letter of the alphabet, not the specific word, and is causing there to
be only 1 search result.

I have 2 tables for the concordance. I have a "words" table and I have a
table that records the words each verse of the Bible contains.

The common field between both tables is:

`bible_concordance_words`.`reference`

equals:

`bible_concordance_word_reference`.`bible_concordance_words_reference`

This is the query so far. What change do I need to make so COUNT() will
tell me the number of times a word is used in the Bible with the design of
my tables?

SELECT `bible_concordance_words`.`reference`,
`bible_concordance_words`.`word`,
COUNT(`bible_concordance_word_reference`.`bible_concordance_words_reference`)
AS occurrences FROM `bible_concordance_words` INNER JOIN
`bible_concordance_word_reference` ON
`bible_concordance_words`.`reference` =
`bible_concordance_word_reference`.`bible_concordance_words_reference`
WHERE `word` LIKE '$letter%' ORDER BY `word` ASC

Thanks for your help.

Ron

From: Richard Quadling on 13 Aug 2010 08:50
On 13 August 2010 13:43, Ron Piggott <ron.piggott(a)actsministries.org> wrote:
> SELECT `bible_concordance_words`.`reference`,
> `bible_concordance_words`.`word`,
> COUNT(`bible_concordance_word_reference`.`bible_concordance_words_reference`)
> AS occurrences FROM `bible_concordance_words` INNER JOIN
> `bible_concordance_word_reference` ON
> `bible_concordance_words`.`reference` =
> `bible_concordance_word_reference`.`bible_concordance_words_reference`
> WHERE `word` LIKE '$letter%' ORDER BY `word` ASC

SELECT
`bible_concordance_words`.`word`,
COUNT(`bible_concordance_word_reference`.`bible_concordance_words_reference`)
AS occurrences
FROM
`bible_concordance_words`
INNER JOIN
`bible_concordance_word_reference` ON
`bible_concordance_words`.`reference` =
`bible_concordance_word_reference`.`bible_concordance_words_reference`
WHERE
`word` LIKE '$letter%'
GROUP BY
`bible_concordance_words`.`word`,
ORDER BY
`word` ASC

--
Richard Quadling.
 | 
Pages: 1
Prev: how to explain such a regular syntax
Next: CURDATE