Solved

find sum of query

Posted on 2010-08-23
16
242 Views
Last Modified: 2012-05-10
find the sum of
i.quantity (integer)

i.subtotal,e.amountpaid,p.estimatedcost  (0.00)
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

Open in new window

0
Comment
Question by:rgb192
  • 8
  • 6
  • 2
16 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33506635
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
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33506658
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

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33506664
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
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:rgb192
ID: 33507126
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)
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33507133
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
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33507154
"add these columns"

You want ALL the original columns plus the new ones?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33507167
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
0
 

Author Comment

by:rgb192
ID: 33507511
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
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33507582
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)?
0
 

Author Comment

by:rgb192
ID: 33509731
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
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33509748

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

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33509759
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

Open in new window

0
 

Author Comment

by:rgb192
ID: 33509929
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
0
 

Author Comment

by:rgb192
ID: 33509951
both queries find the correct sum

could you have a column for sums
in the current query
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33510046
This?

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 () SumQty,
sum(i.subtotal) over () SumTotal,
sum(e.amountpaid) over () SumPaid,
sum(p.estimatedcost) over () 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
0
 

Author Closing Comment

by:rgb192
ID: 33514068
thanks
works perfectly
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…

825 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