###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
###### Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

# sum subtotal,total,cost

Microsoft SQL Server 2005
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
``````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
``````