Solved

sum subtotal,total,cost

Posted on 2010-11-08
8
689 Views
Last Modified: 2012-05-10
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
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 34092337
Hi,

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

Regards
  David
0
 

Author Comment

by:rgb192
ID: 34095222
i want to find
sum of subtotal,
sum of total,
sum of cost
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34096435
Hi,

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

HTH
  David
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:rgb192
ID: 34103183
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
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 34104269
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
 

Author Comment

by:rgb192
ID: 34110737
the sums are the same as the values

I want there to be one sum subtotal, one sum total(debit), one sum cost
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34113898
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
 

Author Closing Comment

by:rgb192
ID: 34220193
thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will useā€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question