COUNT() query help

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: COUNT() query help

On 13 August 2010 13:43, Ron Piggott 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.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php