SQLTeam.com | Weblogs | Forums

Select clause querry

select O.OrderID, (select distinct (count( D.OrderID)) from Orders D where D.OrderID = O.OrderID group by D.OrderID )'ProductQuantity' from OrderDetails O group by OrderID, ProductID;

It is showing the result as below but the predicted result is
10248 1 10248 3
10248 1 10249 2
10249 1
10249 1

and so on it must be select clause and the count must be in select clause.

Is this a school assignment or for a real business need?

hi

no need for that LONG query you wrote ..

hope this helps

please click arrow to the left for DROP Create Data
create table orders 
(
orderid int 
)

create table orderdetails 
(
orderid int , 
productid int
)

insert into orders select 10248
insert into orders select 10249

insert into orderdetails select 10248,123
insert into orderdetails select 10248,456 
insert into orderdetails select 10248,789

insert into orderdetails select 10249,50 
insert into orderdetails select 10249,100
SELECT O.orderid,
       Count(D.orderid)
FROM   orders D
       JOIN orderdetails O
         ON D.orderid = O.orderid
GROUP  BY O.orderid 

image