Nested SELECT query that also returns COUNT from related table

OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!Use an aggregate query:

select Category.CatID,
Category.Name,
...
count(distinct Product.ProductID) ProductCount
from Categories
left outer join Products on Categories.CategoryID = Products.CategoryID
group by Category.CatID,
Category.Name,
...
order by count(distinct Product.ProductID)|||Absolutely brilliant, it works fantastically, thank you so much!! :D

Now to try and figure out how it actually works :)|||If you use Books Online to figure out how this query works, you can consider yourself to have passed SQL 101. It incorporates the most fundamental aspects of SQL programming.