Solved

find sum of query

Posted on 2010-08-23
16
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to eliminate duplicates in a string variable in t-sql? 30 87
Getting max record but maybe not use Group BY 2 38
Parse this column 6 38
Tracking Problematic Page Splits 1 44
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

740 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