counting... with a difference!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

counting... with a difference!

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-25 : 18:06:20

Hi there,
im trying to create a stored procedure to select the number of orders for each product.

the table structure is as follows:

tblProducts - contains primary key ProductID and ProductName
tblOrders - Containts primary key OrderID
tblOrdersProducts - contains foreign key OrderID, and ProductID

i need to get something like:

---------------

SELECT Count(ProductID) AS TotalOrders FROM tblOrdersProducts WHERE tblOrdersProducts.OrderID = tblOrders.OrderID AND tblOrdersProducts.ProductID = tblProducts.ProductID

---------------

I want to create a recordset with:

ProductID | TotalOrders

if there are no orders, totla orders should be 0 for each product.

Any help is much appreciated.

Regards,

Alex

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 19:02:42

[code]
select p.ProductID, TotalOrders = isnull(sum(OrdersCnt), 0)
from tblProducts p
left join
(
select ProductID, OrdersCnt = count(distinct o.OrderID) -- count the distinct orderid
from tblOrders o inner join tblOrdersProduts p
on o.OrderID = p.OrderID
group by ProductID
) o
on p.ProductID = o.ProductID
[/code]

or if you want to count the coinsurance of product in the orders
[code]
select p.ProductID, Total = isnull(count(*), 0)
from tblProducts p left join tblOrdersProduts o
on p.ProductID = o.ProductID
group by p.ProductID
[/code]

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-26 : 04:52:01

Excellent, works great.
how can i multiply the "Total" count by the Qty column in the tblOrdersProducts?

current recordset returns number of orders for each product id, however customer could order 10 of that product id.

does that make sense?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-26 : 09:34:19

this ?


select p.ProductID, Total = isnull(sum(o.Qty), 0)
from   tblProducts p left join tblOrdersProduts o
on     p.ProductID = o.ProductID
group by p.ProductID

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-26 : 09:45:05

perfect, spot on.
thank you.

alex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources