find sum of query

select sum(quantity)
from
(
select o.dateordered,o.orderid,s.status,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,
o.itemid,e.title,p.internalsku,i.quantity,i.subtotal,e.amountpaid,p.estimatedcost,p.productid
from orders o left join ebaydata e on o.orderid = e.orderid
LEFT JOIN orderitems i on o.orderid = i.orderid
LEFT JOIN products p on i.productid = p.productid left join payments y on o.orderid = y.orderid
left join statuses2 s on o.statusid=s.statusid
where (o.dateordered between convert ( datetime, '16/08/2010', 103 )
 and convert ( datetime, '23/08/2010', 103 )+1) and o.statusid in (9,10,90,180,260)
and company like '%' + 'company' + '%'
--order by o.dateordered
) X

Not exactly sure what you mean. If you want the sum of the above fields for orders that match the constraint, this should work:

select
	o.orderid,
	ISNULL(SUM(i.quantity), 0) AS SumQuantity,
	ISNULL(SUM(i.subtotal), 0) AS SumSubTotal,
	ISNULL(SUM(e.amountpaid), 0) AS SumAmountPaid
	ISNULL(SUM(p.estimatedcost), 0) AS SumEstimatedCost
from orders o 
left join ebaydata e 
	on o.orderid = e.orderid 
LEFT JOIN orderitems i 
	on o.orderid = i.orderid 
LEFT JOIN products p 
	on i.productid = p.productid 
left join payments y 
	on o.orderid = y.orderid 
left join 
	statuses2 s on o.statusid=s.statusid 
where 
	(o.dateordered between convert ( datetime, '16/08/2010', 103 ) and convert ( datetime, '23/08/2010', 103 )+1)
	and o.statusid in (9,10,90,180,260) and 
	company like '%' + 'company' + '%' 
group by
	o.orderid

Select allOpen in new window

select o.dateordered,o.orderid,s.status,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,
o.itemid,e.title,p.internalsku,
sum(i.quantity) SumQuantity,
sum(i.subtotal) SumSubtotal,
sum(e.amountpaid) SumAmountpaid,
sum(p.estimatedcost) Sumestimatedcost, p.productid
from orders o left join ebaydata e on o.orderid = e.orderid
LEFT JOIN orderitems i on o.orderid = i.orderid
LEFT JOIN products p on i.productid = p.productid left join payments y on o.orderid = y.orderid
left join statuses2 s on o.statusid=s.statusid
where (o.dateordered between convert ( datetime, '16/08/2010', 103 )
 and convert ( datetime, '23/08/2010', 103 )+1) and o.statusid in (9,10,90,180,260)
and company like '%' + 'company' + '%'
Group by o.dateordered,o.orderid,s.status,o.billfirstname,o.billlastname,o.itemid,e.title,p.internalsku,p.productid
order by o.dateordered

select
      o.orderid,
      ISNULL(SUM(i.quantity), 0) AS SumQuantity,
      ISNULL(SUM(i.subtotal), 0) AS SumSubTotal,
      ISNULL(SUM(e.amountpaid), 0) AS SumAmountPaid
      ISNULL(SUM(p.estimatedcost), 0) AS SumEstimatedCost
from orders o
left join ebaydata e
      on o.orderid = e.orderid
LEFT JOIN orderitems i
      on o.orderid = i.orderid
LEFT JOIN products p
      on i.productid = p.productid
left join payments y
      on o.orderid = y.orderid
left join
      statuses2 s on o.statusid=s.statusid
where
      (o.dateordered between convert ( datetime, '16/08/2010', 103 ) and convert ( datetime, '23/08/2010', 103 )+1)
      and o.statusid in (9,10,90,180,260) and
      company like '%' + 'company' + '%'
group by
      o.orderid


Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'ISNULL'.
















select o.dateordered,o.orderid,s.status,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,
o.itemid,e.title,p.internalsku,
sum(i.quantity) SumQuantity,
sum(i.subtotal) SumSubtotal,
sum(e.amountpaid) SumAmountpaid,
sum(p.estimatedcost) Sumestimatedcost, p.productid
from orders o left join ebaydata e on o.orderid = e.orderid
LEFT JOIN orderitems i on o.orderid = i.orderid
LEFT JOIN products p on i.productid = p.productid left join payments y on o.orderid = y.orderid
left join statuses2 s on o.statusid=s.statusid
where (o.dateordered between convert ( datetime, '16/08/2010', 103 )
 and convert ( datetime, '23/08/2010', 103 )+1) and o.statusid in (9,10,90,180,260)
and company like '%' + 'company' + '%'
Group by o.dateordered,o.orderid,s.status,o.billfirstname,o.billlastname,o.itemid,e.title,p.internalsku,p.productid
order by o.dateordered



is the same output as the query i started with

I want to add these columns to my current output

sum of
i.quantity (integer)

sum of
i.subtotal,e.amountpaid,p.estimatedcost  (0.00)

select
      o.orderid,
      ISNULL(SUM(i.quantity), 0) AS SumQuantity,
      ISNULL(SUM(i.subtotal), 0) AS SumSubTotal,
      ISNULL(SUM(e.amountpaid), 0) AS SumAmountPaid,
      ISNULL(SUM(p.estimatedcost), 0) AS SumEstimatedCost
from orders o
left join ebaydata e
      on o.orderid = e.orderid
LEFT JOIN orderitems i
      on o.orderid = i.orderid
LEFT JOIN products p
      on i.productid = p.productid
left join payments y
      on o.orderid = y.orderid
left join
      statuses2 s on o.statusid=s.statusid
where
      (o.dateordered between convert ( datetime, '16/08/2010', 103 ) and convert ( datetime, '23/08/2010', 103 )+1)
      and o.statusid in (9,10,90,180,260) and
      company like '%' + 'company' + '%'
group by
      o.orderid

"add these columns"

You want ALL the original columns plus the new ones?

For the sum, if you want it by unique orderid, then

select o.dateordered,o.orderid,s.status,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,
o.itemid,e.title,p.internalsku,i.quantity,i.subtotal,e.amountpaid,p.estimatedcost,p.productid,
sum(i.quantity) over (partition by o.orderid) SumQty,
sum(i.subtotal) over (partition by o.orderid) SumTotal,
sum(e.amountpaid) over (partition by o.orderid) SumPaid,
sum(p.estimatedcost) over (partition by o.orderid) SumEstimate
from orders o left join ebaydata e on o.orderid = e.orderid
LEFT JOIN orderitems i on o.orderid = i.orderid
LEFT JOIN products p on i.productid = p.productid left join payments y on o.orderid = y.orderid
left join statuses2 s on o.statusid=s.statusid
where (o.dateordered between convert ( datetime, '16/08/2010', 103 )
 and convert ( datetime, '23/08/2010', 103 )+1) and o.statusid in (9,10,90,180,260)
and company like '%' + 'company' + '%'
order by o.dateordered

Adjust the partition clause as required

i meant sum of a column
i.quantity (integer)

sum of columns
i.subtotal,e.amountpaid,p.estimatedcost  (0.00)



for example
there are alot of 1 quantity in 'quantity'
so the sum of quantity is 50
(1+1+1+1+1+...)=50

If you could please answer both questions, I can try again.

"add these columns"
You want ALL the original columns plus the new ones?  Or does the result only need the 4 columns, i.e. the 4 sums?

Is your SUM across the whole dataset, so 1 single result record from the query, or multiple records, 1 for each orderid (or some combination of fields)?

Or does the result only need the 4 columns, i.e. the 4 sums


Is your SUM across the whole dataset, so 1 single result record from the query

select sum(quantity) SumQuantity, sum(subtotal) SumSubTotal, sum(amountpaid) SumAmountPaid, sum(estimatedcost) SumEstimatedCost
from
(
select o.dateordered,o.orderid,s.status,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,
o.itemid,e.title,p.internalsku,i.quantity,i.subtotal,e.amountpaid,p.estimatedcost,p.productid
from orders o left join ebaydata e on o.orderid = e.orderid
LEFT JOIN orderitems i on o.orderid = i.orderid
LEFT JOIN products p on i.productid = p.productid left join payments y on o.orderid = y.orderid
left join statuses2 s on o.statusid=s.statusid
where (o.dateordered between convert ( datetime, '16/08/2010', 103 )
 and convert ( datetime, '23/08/2010', 103 )+1) and o.statusid in (9,10,90,180,260)
and company like '%' + 'company' + '%'
--order by o.dateordered
) X

Select allOpen in new window

If by
i.subtotal,e.amountpaid,p.estimatedcost  (0.00)
You mean 2 decimal places then

select
sum(quantity) SumQuantity,
convert(decimal(12,2),sum(subtotal)) SumSubTotal,
convert(decimal(12,2),sum(amountpaid)) SumAmountPaid,
convert(decimal(12,2),sum(estimatedcost)) SumEstimatedCost
from
(
select o.dateordered,o.orderid,s.status,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,
o.itemid,e.title,p.internalsku,i.quantity,i.subtotal,e.amountpaid,p.estimatedcost,p.productid
from orders o left join ebaydata e on o.orderid = e.orderid
LEFT JOIN orderitems i on o.orderid = i.orderid
LEFT JOIN products p on i.productid = p.productid left join payments y on o.orderid = y.orderid
left join statuses2 s on o.statusid=s.statusid
where (o.dateordered between convert ( datetime, '16/08/2010', 103 )
 and convert ( datetime, '23/08/2010', 103 )+1) and o.statusid in (9,10,90,180,260)
and company like '%' + 'company' + '%'
--order by o.dateordered
) X

Select allOpen in new window

select
sum(quantity) SumQuantity,
convert(decimal(12,2),sum(subtotal)) SumSubTotal,
convert(decimal(12,2),sum(amountpaid)) SumAmountPaid,
convert(decimal(12,2),sum(estimatedcost)) SumEstimatedCost
from
(
select o.dateordered,o.orderid,s.status,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,
o.itemid,e.title,p.internalsku,i.quantity,i.subtotal,e.amountpaid,p.estimatedcost,p.productid
from orders o left join ebaydata e on o.orderid = e.orderid
LEFT JOIN orderitems i on o.orderid = i.orderid
LEFT JOIN products p on i.productid = p.productid left join payments y on o.orderid = y.orderid
left join statuses2 s on o.statusid=s.statusid
where (o.dateordered between convert ( datetime, '16/08/2010', 103 )
 and convert ( datetime, '23/08/2010', 103 )+1) and o.statusid in (9,10,90,180,260)
and company like '%' + 'company' + '%'
--order by o.dateordered
) X



is the correct result for the sum

could you add the sum to the current query

both queries find the correct sum

could you have a column for sums
in the current query

thanks
works perfectly