SUM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SUM

Author

Topic

sambrown180
Starting Member

38 Posts

Posted - 2008-11-18 : 11:16:06

Sorry if I am repeating previous posts.

I am trying to make a table that will calculate a total price.
I have a price column, and a discount column (Both are money data types should they be int?) I then have a column which has a c sub total which needs to basically have price-discount. Is it possible for the cell to work this out for me? What code would be needed?

I then have a seperate table for orders (the previous paragraph described each line of an order) is it possible to have a grand total which will calculate mutliple sub totals from another table.

Please help as this is killing me trying to figure this out.

Thanks

Sam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:20:01

Are you talking about table container in sql reports? if yes, just use

=Fields!price.value-Fields!discount.value
for subtotal

and for orders grandtotal just use
=SUM(Fields!price.value-Fields!discount.value)

sambrown180
Starting Member

38 Posts

Posted - 2008-11-18 : 11:21:52

I am not looking at reports although i may have to. I was wondering if the table itself would be able to do any calculations or is that not possible? e.g. column 1 column 2 column 3
price discount new total

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:23:48

quote:
Originally posted by sambrown180

I am not looking at reports although i may have to. I was wondering if the table itself would be able to do any calculations or is that not possible? e.g. column 1 column 2 column 3
price discount new total

just use

SELECT Price,Discount,Price-Discount AS NewTotal
FROM YourTable

and for grandtotal use

SELECT SUM(Price-Discount)
FROM table

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 11:24:27

quote:
Sample data,output and structure of tables will make it easier to help you out. Regardless, You could frame something like this--

select [price column],[discount column],SellPrice=[price column]-[discount column]
from
<YourLinetable>

select b.OrderId,SellPrice=sum(a.[price column]-a.[discount column])
from
<YourLinetable> a
join <YourOrderTable> b on a.LineId=b.LineId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:29:20

quote:
Originally posted by sakets_2000

quote:
Sample data,output and structure of tables will make it easier to help you out. Regardless, You could frame something like this--

select [price column],[discount column],SellPrice=[price column]-[discount column]
from
<YourLinetable>

select b.OrderId,SellPrice=sum(a.[price column]-a.[discount column])
from
<YourLinetable> a
join <YourOrderTable> b on a.LineId=b.LineId

the second query is syntactically incorrect. You cant use OrderId directly while using aggregate function SUM() on same select list. you need to add it to group by if you want to use it like that. but that will screw up the grandtotal.

sambrown180
Starting Member

38 Posts

Posted - 2008-11-18 : 11:31:18

Ok any suggestions on how to get round this then

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 11:37:17

quote:
Originally posted by sambrown180

Ok any suggestions on how to get round this then

missed the group by expression.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:38:05

quote:
Originally posted by sambrown180

Ok any suggestions on how to get round this then

how do you want to show final data? can you post sample output?

sambrown180
Starting Member

38 Posts

Posted - 2008-11-18 : 11:46:56

I have two tables one called order and one called order line which each line can show a seperate product for the same order. So each orderId can be used more than once in order line.

Order:

orderId, GrandTotal (somehow this needs to come from order line sub totals)

OrderLine:

lineID,OrderID, productID, price, discount, subtotal (subtotal comes from price-discount)

ideally it would look like

Order Table

1, £15.00

Order line Table:
lineId, orderID, procutId, price, discount, subtotal
1, 1, 1, £20.00, £10.00, £10.00
2, 1, 2, £10.00, £5.00, £5.00

If it would make like easier discount could always be the same amount.

sambrown180
Starting Member

38 Posts

Posted - 2008-11-18 : 11:48:02

Sorry for the mess that came out like each column is in order with the data in the same order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:55:09

[code]SELECT ol.OrderID,ol.productID, ol.price, ol.discount, ol.price-ol.discount as subtotal,o.GrandTotal
FROM OrderLine ol
INNER JOIN (SELECT OrderID,SUM( price-discount ) AS GrandTotal
FROM OrderLine
GROUP BY OrderID)o
ON o.OrderID=ol.OrderID[/code]

and if sql 2005 just use
[code]
SELECT ol.OrderID,ol.productID, ol.price, ol.discount, ol.price-ol.discount as subtotal,SUM(ol.price-ol.discount) OVER (PARTITION BY ol.OrderID) AS GrandTotal
FROM OrderLine ol
[/code]

sambrown180
Starting Member

38 Posts

Posted - 2008-11-18 : 11:57:06

I am trying to make the table do the work though is there nothing that can fill the cells in automatically?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:58:33

quote:
Originally posted by sambrown180

I am trying to make the table do the work though is there nothing that can fill the cells in automatically?

which cells are you talking about?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources