COUNT() question

Hello,

I need to write a SELECT statement that will display the most popular
categories.

This means I need a 'category count' for each of the messages in the
messages table, and I don't know how to do this.

Here is the structure of the 2 tables:

messages table
id, title, message, category, thread, status, date_posted

categories table
id, title, sub_cat

I've tried the following:
select categories.id, categories.title, count(messages.category) as
count from messages, categories group by categories.title;

but 'count' only returns the total count of messages with a category
entry, and I need the number of times each category id is entered in
the messages table.

Any help is greatly appreciated. Also, does anyone have any online
references for learning more complex SQL statements?

Thanks,
Aaron

Re: COUNT() question

use group by messages.category instead of categories.title

select c.id, c.title, count(m.category) as cnt from messages m
left join categories c
on c.id=3Dm.category
group by m.category
order by cnt desc

acorn71 íàïèñà=E2:
> Hello,
>
> I need to write a SELECT statement that will display the most popular
> categories.
>
> This means I need a 'category count' for each of the messages in the
> messages table, and I don't know how to do this.
>
> Here is the structure of the 2 tables:
>
> messages table
> id, title, message, category, thread, status, date_posted
>
> categories table
> id, title, sub_cat
>
> I've tried the following:
> select categories.id, categories.title, count(messages.category) as
> count from messages, categories group by categories.title;
>
> but 'count' only returns the total count of messages with a category
> entry, and I need the number of times each category id is entered in
> the messages table.
>
> Any help is greatly appreciated. Also, does anyone have any online
> references for learning more complex SQL statements?
>=20
> Thanks,
> Aaron

Re: COUNT() question

que wrote:
> use group by messages.category instead of categories.title
>
> select c.id, c.title, count(m.category) as cnt from messages m
> left join categories c
> on c.id=m.category
> group by m.category
> order by cnt desc
>

Thank you for your response, que. That statement worked great!

Does anyone have any suggestions for me as to online resources where I
can learn how to execute the more challenging SQL queries. I find the
manual at mysql.com very useful, but sometimes I don't know where to
begin to look, and I just want to write the most effecient SQL commands
so my code is clean and I can minimize server-side logic.

Thanks,
Aaron

Re: COUNT() question

acorn71 wrote:
[snipped]
> begin to look, and I just want to write the most effecient SQL commands
> so my code is clean and I can minimize server-side logic.

It is always more efficient to only return data that the client actually needs
rather than return a lot that gets tossed (processed). Let the database engine
do what it is designed to do... manage your data and you will do well.

>
> Thanks,
> Aaron
>


--
Michael Austin.
Database Consultant