Help with this report query for sum data

[Bentk]

I am trying to create a query to pull up not only the unit cost but also the sum total of an order (sum(unitcost)) and return it to the result set for every line Item. I am writing this for a report and I will filter the data there. I am trying to avoid using a sub report. Here is where I am at so far. I have adapted my example for the nothwind db.

select
Sum (od.unitprice),
e.lastname+', ' + e.firstname as emloyee,
od.orderid,
p.productname,
od.unitprice
from orders o
join orderdetails od on o.orderid=od.orderid
join employees e on o.employeeid=e.employeeid
join products p on od.productid=p.productid
where o.orderid = 10248
group by
od.orderid,
e.lastname,
e.firstname,
od.unitprice,
p.productname

 

[vbkris]

>>and return it to the result set for every line Item.

you should use subqueries for this. I am not aware of the design of Northwind DB. Therefore if you can explain what the tables are then i can provide a solution...

Known is handfull, Unknown is worldfull

 

[Bentk]

Thanks for the hint. That got it to work. Here is the code.

declare @ordernum int
select @ordernum = 10248
select e.lastname+', ' + e.firstname as emloyee,
od.orderid,
p.productname,
(select Sum (unitprice)from orderdetails
where orderdetails.orderid = @ordernum)as ordertotal,
od.unitprice
from orders o
join orderdetails od on o.orderid=od.orderid
join employees e on o.employeeid=e.employeeid
join products p on od.productid=p.productid
where o.orderid = @ordernum

 

[vbkris]

welcome...

Known is handfull, Unknown is worldfull