Solved

sum subtotal,total,cost

Posted on 2010-11-08
8
688 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

860 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