summing across mulitiple tables.... 1

[jenfiers]

I am trying to get the total cost for each order per customer. here is as far as I have been able to get...
select
ordertbl.ordno
, ordertbl.orddate
, sum(ordline.qty)
, product.prodprice
, sum(ordline.qty * product.prodprice)
from
ordertbl
, ordline
, product
where
ordertbl.ordno = 'O1231231'
and
ordertbl.ordno = ordline.ordno
and
(product.prodno = ordline.PRODNO) and (ordline.ordno = ordertbl.ordno)
group by
ordertbl.ordno
, ordertbl.orddate
, product.prodprice

ORDNO ORDDATE SUM(ORDLINE.QTY) PRODPRICE SUM(ORDLINE.QTY*P
-------- --------------------- ---------------- -------------- -----------------
O1231231 1/23/2004 1 14.99
O1231231 1/23/2004 1 169.00

I need this to be one line that shows 2 items ordered for a total of 183.99 any help would be appreciated...

 

[Kaiserr2]

Well you could try doing something like this:

change the original select statement to
ordertbl.ordno
, ordertbl.orddate
, sum(ordline.qty) as Quantity
, product.prodprice
, sum(ordline.qty * product.prodprice) as Total

and then create another sql with the original and a sub select:
select
sum(Quantity)
, sum(Total)
from
(copy and paste your SQL in here)

 

[aryeh1010]

Just remove the product.prodprice from the select and group by sections of your query. That is what is causing multiple rows, where you want only one.