Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# calculate the total price

Posted on 2008-11-11
Medium Priority
164 Views
Hello Experts,

I have a query which pulls information on the orders sent until today, Please can someone help me in finding the sum ordertotal, sum of ordershipping  and sum of orderlineprice

All I wanted the total amount between 1-sep-2007 to 31-aug-08

thanks
kay
``````SELECT     dbo.[Order].OrderID, dbo.[Order].OrderDate, dbo.[Order].OrderTotal, dbo.[Order].OrderShipping, dbo.OrderDetail.OrderLineProdName,
dbo.OrderDetail.OrderLineQty, dbo.OrderDetail.OrderLineIncVAT, dbo.OrderDetail.RowID, dbo.OrderDetail.OrderLinePrice
FROM         dbo.[Order] INNER JOIN
dbo.OrderDetail ON dbo.[Order].OrderID = dbo.OrderDetail.OrderID
WHERE     (dbo.[Order].OrderIncludeInWeeklySummary = 0)
``````
0
Question by:learningnet
• 2
• 2

LVL 60

Accepted Solution

chapmandew earned 1000 total points
ID: 22932636
SELECT
dbo.[Order].OrderID,
dbo.[Order].OrderDate,
dbo.[Order].OrderTotal,
dbo.[Order].OrderShipping,
dbo.OrderDetail.OrderLineProdName,
dbo.OrderDetail.OrderLineQty,
dbo.OrderDetail.OrderLineIncVAT,
dbo.OrderDetail.RowID,
dbo.OrderDetail.OrderLinePrice,
a.*
FROM
dbo.[Order]
INNER JOIN dbo.OrderDetail ON dbo.[Order].OrderID = dbo.OrderDetail.OrderID
INNER JOIN (
select
orderid,
sum(ordertotal) as ordertotal,
sum(ordershipping) as ordershippingtotal,
sum(orderlineprice) as orderlinepricetotal
from
orderdetail
group by
orderid
) a ON [Order].OrderID = a.[OrderID]
WHERE
(dbo.[Order].OrderIncludeInWeeklySummary = 0)
0

LVL 61

Assisted Solution

Kevin Cross earned 1000 total points
ID: 22932778
Or something ike this if you only want the sum through period you specified.
``````SELECT    SUM(dbo.[Order].OrderTotal) AS SumOrderTotal
, SUM(dbo.[Order].OrderShipping) AS SumOrderShipping
, SUM(dbo.OrderDetail.OrderLinePrice) AS SumOrderLinePrice
FROM         dbo.[Order] INNER JOIN
dbo.OrderDetail ON dbo.[Order].OrderID = dbo.OrderDetail.OrderID
WHERE     (dbo.[Order].OrderIncludeInWeeklySummary = 0)
AND dbo.[Order].OrderDate >= '2007-09-01 00:00:00' AND dbo.[Order].OrderDate < '2008-09-01 00:00:00'
``````
0

LVL 60

Expert Comment

ID: 22932786
Good addition Kevin...wasn't sure if the author wanted to keep the current detail they had or not.  I almost wrote the same thing you just did.  ;)
0

LVL 61

Expert Comment

ID: 22933394
:) Yes, I am not sure either.  Think what you wrote is good too.
0

## Featured Post

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦