Posted by Hugo Kornelis on 01/13/06 00:07

On 12 Jan 2006 13:51:10 -0800, pipe.jack@gmail.com wrote:

>Hello guys,
>
>I'm not an sql expert but I need to pull out some data from SQL
>database. I hope you can help me here.
>I need to find out how many time a product was ordered. Some product
>have the same ProductName but different Option and for me that's a
>different product.
>
>Here is the database:
>
>- TABLE OrderDetails -
>OrderDetailID
>OrderID
>ProductName
>Options
>
>- TABLE Orders -
>OrderID
>CustomerID
>KeyAccess
>
>The output should look something like this:
>NoOrders ProductName ProductOptions KeyAccess
>12 Blue car $500 C
>7 Blue car $700
>C
>3 Yellow car $1000 C
>1 Yellow car $5000 C
>.........................
>C = customers
>
>
>Thanks for any help
>Jack

Hi Jack,

Try if this works:

SELECT COUNT(*) AS NoOrders,
od.ProductName, od.Options, o.KeyAccess
FROM OrderDetails AS od
INNER JOIN Orders AS o
ON o.OrderID = od.OrderID
GROUP BY od.ProductName, od.Options, o.KeyAccess

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[Back to original message]