Solved

find sum of query

Posted on 2010-08-23
16
239 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:rgb192
Comment Utility
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
Comment Utility
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
Comment Utility
"add these columns"

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

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
works perfectly
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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