• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 715
  • Last Modified:

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

Open in new window

0
rgb192
Asked:
rgb192
  • 4
  • 4
1 Solution
 
David ToddSenior DBACommented:
Hi,

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

Regards
  David
0
 
rgb192Author Commented:
i want to find
sum of subtotal,
sum of total,
sum of cost
0
 
David ToddSenior DBACommented:
Hi,

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

HTH
  David
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
rgb192Author Commented:
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

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

Sorry, I didn't see the order by clause on line 25.

For now comment it out.

Otherwise line 1 should read
select OrderID, sum( subtotal ), sum( total ), sum( cost ) from (

change line 25 to

group by
    orderID
order by
    orderID
;

HTH
  David

PS For me to help quicker/better, I need some sample data. That is, orders, orderitems, products, ebaydata, sstatuses, payments. Posting them in the form of SQL create tables/insert tables will help.
0
 
rgb192Author Commented:
the sums are the same as the values

I want there to be one sum subtotal, one sum total(debit), one sum cost
0
 
David ToddSenior DBACommented:
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
0
 
rgb192Author Commented:
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now