sum subtotal,total,cost
subtotal is derived line 12
debit is derived line 13
minitemid is derived line 14
if i.itemid==minitemid
then total=debit and subtotal=subtotal
else total=0 and subtotal=0
I am doing this because I only want the total and subtotal to be on one row
There can be many orderitems.itemid for one orders.orderid but there is only one total and subtotal per order
also want to find sum of subtotal as subtotalsum, total as totalsum, cost as costsum
debit is derived line 13
minitemid is derived line 14
if i.itemid==minitemid
then total=debit and subtotal=subtotal
else total=0 and subtotal=0
I am doing this because I only want the total and subtotal to be on one row
There can be many orderitems.itemid for one orders.orderid but there is only one total and subtotal per order
also want to find sum of subtotal as subtotalsum, total as totalsum, cost as costsum
select * from(
select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid
,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle
,e.title as etitle
,s.status
,i.sku,i.quantity,i.itemid as iitemid
,CASE sku
WHEN p.internalsku1 THEN p.cost1*i.quantity
WHEN p.internalsku2 THEN p.cost2*i.quantity
WHEN p.internalsku3 THEN p.cost3*i.quantity
ELSE 0 END as cost
,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal
,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and 'confirmation'!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit
,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid
from orders o
INNER JOIN orderitems i on i.orderid = o.orderid
INNER JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where (o.dateordered between '11/01/2010' and '11/09/2010')
AND o.orderid In (select orderid from orderitems i,Products P Where i.productid = p.productid and P.title LIKE '%sd%')
and o.statusid in (10,90,110,260)
and ( (company='company1'and o.itemid>0) or (company='company2'and o.itemid>0) or o.itemid=0 )
)Derived where debit>0
order by orderid desc
ASKER
i want to find
sum of subtotal,
sum of total,
sum of cost
sum of subtotal,
sum of total,
sum of cost
Hi,
On line 1 replace select * from (
with select sum( subtotal ), sum( total ), sum( cost ) from (
HTH
David
On line 1 replace select * from (
with select sum( subtotal ), sum( total ), sum( cost ) from (
HTH
David
ASKER
Msg 8127, Level 16, State 1, Line 1
Column "Derived.orderid" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Column "Derived.orderid" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
select sum(subtotal) from(
select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid
,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle
,e.title as etitle
,s.status
,i.sku,i.quantity,i.itemid as iitemid
,CASE sku
WHEN p.internalsku1 THEN p.cost1*i.quantity
WHEN p.internalsku2 THEN p.cost2*i.quantity
WHEN p.internalsku3 THEN p.cost3*i.quantity
ELSE 0 END as cost
,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal
,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and 'confirmation'!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit
,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid
from orders o
INNER JOIN orderitems i on i.orderid = o.orderid
INNER JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where (o.dateordered between '11/01/2010' and '11/09/2010')
AND o.orderid In (select orderid from orderitems i,Products P Where i.productid = p.productid and P.title LIKE '%sd%')
and o.statusid in (10,90,110,260)
and ( (company='company1'and o.itemid>0) or (company='company2'and o.itemid>0) or o.itemid=0 )
)Derived where debit>0
order by orderid desc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the sums are the same as the values
I want there to be one sum subtotal, one sum total(debit), one sum cost
I want there to be one sum subtotal, one sum total(debit), one sum cost
Hi,
I can't really comment without sample data.
If you want one total line, then instead of the previous instructions, remove the order by clause on line 25. I suggested commenting it out. If you had done this you would have solved your problem.
HTH
David
I can't really comment without sample data.
If you want one total line, then instead of the previous instructions, remove the order by clause on line 25. I suggested commenting it out. If you had done this you would have solved your problem.
HTH
David
ASKER
thanks.
I don't see a question. What is your problem with the query?
Regards
David