  asked on

# 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
``````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
``````
Microsoft SQL Server 2005 Last Comment
rgb192

8/22/2022 - Mon
David Todd

Hi,

I don't see a question. What is your problem with the query?

Regards
David
rgb192

i want to find
sum of subtotal,
sum of total,
sum of cost
David Todd

Hi,

On line 1 replace select * from (
with  select sum( subtotal ), sum( total ), sum( cost ) from (

HTH
David
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
rgb192

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.
``````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
``````
David Todd

Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
rgb192

the sums are the same as the values

I want there to be one sum subtotal, one sum total(debit), one sum cost
David Todd

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
Get an unlimited membership to EE for less than \$4 a week.
Unlimited question asking, solutions, articles and more.