Solved

sum subtotal,total,cost

Posted on 2010-11-08
8
685 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now