COUNT() question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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

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.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?


Re: COUNT() question

use group by messages.category instead of categories.title

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

Quoted text here. Click to load it

Re: COUNT() question

que wrote:
Quoted text here. Click to load it

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 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.


Re: COUNT() question

acorn71 wrote:
Quoted text here. Click to load it

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.

Quoted text here. Click to load it

Michael Austin.
Database Consultant

Site Timeline