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
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
Last Comment
ASKER
i want to find
sum of subtotal,
sum of total,
sum of cost
sum of subtotal,
sum of total,
sum of cost
Hi,
On line 1 replace select * from (
with select sum( subtotal ), sum( total ), sum( cost ) from (
HTH
David
On line 1 replace select * from (
with select sum( subtotal ), sum( total ), sum( cost ) from (
HTH
David
ASKER
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.
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
the sums are the same as the values
I want there to be one sum subtotal, one sum total(debit), one sum cost
I want there to be one sum subtotal, one sum total(debit), one sum cost
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
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
ASKER
thanks.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
I don't see a question. What is your problem with the query?
Regards
David