select
x.OrderID
,Nof_Distinct_Process
,sum(NOf_Items) 
from
(
 Select 
  OrderID as orderID
  ,count(distinct ProcessID) as NOf_distinct_Process
  ,count(ItemID) as NOf_Items
   from Order 
   inner join Process
   on Order.OrderID=Process.OrderID 
   inner join
   Items on Process.ProcessID=Items.ProcessID
   group by Order.OrderID.
) as x
group by 
 x.OrderID
,x.Nof_Distinct_Process

quote:

---

Originally posted by MangoSkin

I have 3 tables Order,Process,Items
Order has one to many relation with Process,
Process has one to many relation with Items.

I want to see the number of Items in an Order.
Select OrderID,count(ProcessID),sum(count(ItmeID))over()
from Order inner join Process
on Order.OrderID=Process.OrderID inner join
Items on Process.ProcessID=Items.ProcessID
group by Order.OrderID.

I am having issue with summing the Items part.
Can someone help?

---

quote:

---

Originally posted by webfred

What should that be?
sum(count(ItmeID))over()

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---

declare @tbl as table(orderid int,itemid int)
insert into @tbl
select 1,1 union all
select 2,3 union all
select 3,4 union all
select 1,5

select orderid,SUM(count(itemid))over() from @tbl
group by orderid

quote:

---

Originally posted by slimt_slimt

Idera,

this query sure is valid and okey, but i hope the MangoSkin understands the value and results of the query. :)

---