Simple SQL help

[rathtap]

I have a Products table that has ProductID(PK) and Description fields.
Another table Orders has OrderID(PK),ProductID(Foriegn Key) and some other fields.

I want to create a join between the two tables that gives me ProductID, Description and the NumInst (a count of ProductID in
Orders).

It should be pretty simple SQL but I have been having a tough time over it. I get a
" you tried to execute a query that does not
include the specified expression 'ProductID' as part of an aggregate function " error.

[chrisjk]

Something like this should work:

SELECT Sum(Orders.ProductsID) AS SumOfProductsID, Products.Desc FROM Products INNER JOIN Orders ON Products.ProductsID = Orders.ProductsID GROUP BY Products.Desc ORDER BY Sum(Orders.ProductsID);

[rathtap]

Chris,
There is. It establishes the relationship
between Products and Orders. It is a one to many
- one ProductID in Products ---> many in Orders
(because any number of orders can be for the same
product).

I will look at my SQL again - maybe I am doing something
wrong because I gave you a simplified version of the tables
and fields.

[chrisjk]

Well I created some tables exactly as you described them, made some fake products and orders, then ran the query and it displayed the product description with how many times that produt appears in the orders table.

I've modified it very slightly, try this:

SELECT Sum(Orders.ProductsID) AS SumOfProductsID, Products.Desc
FROM Products LEFT JOIN Orders ON Products.ProductsID = Orders.ProductsID
GROUP BY Products.Desc
ORDER BY Sum(Orders.ProductsID);

[rathtap]

Thanks,
I am off to lunch and will try it in an hours time.

[rathtap]

That works great!!
How can I also have the ProductID show up?

[rathtap]

I added Group by ProductID and that brings up that field too.

But I have another question. My Orders table has a date field
OrderDate. By including OrderDate=? I can run a query based on the date for which the user wants the data. How can I expand this to accept a range of dates?

[rathtap]

I got the answer to that too:
where OrderDate Between ? and ?