Solved

find sum of query

Posted on 2010-08-23
16
246 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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