tmalmond
asked on
How to sum a calculated field in sql server
I am trying to get the sum of a calculated column in sql server. It seems like it should be relatively simple, but when I try to sum the calculated column, it errors out. I have tried several different methods. My select looks like this:
select
ReqNum as 'Req#',
LnItemNum as 'Line#',
lnqty as 'Qty',
LnDesc as 'Description',
lnprice as 'Price',
(LnQty*LnPrice) as 'Extension'
from tblreq,
tblLineItem
where tblreq.ReqNum = tblLineItem.LnReqNum and tblreq.ReqNum = 2006617
order by tblLineItem.LnItemNum
The query yields this result:
2006617 1 1 Keytronic 17.55 17.55
2006617 2 2 Intel STS 27.24 54.48
2006617 3 1 Intel SC 327.87 327.87
2006617 4 1 Sony 15.64 15.64
2006617 5 6 Kingsto 53.46 320.76
2006617 6 1 Window 142.63 142.63
2006617 7 1 Viewso 159.31 159.31
2006617 8 1 Viewso -20.00 -20
2006617 9 1 Intel S5 417.27 417.27
2006617 10 2 Intel 1216.01 2432.02
2006617 11 4 WD45 235.85 943.4
I am trying to sum the 'Extension' column to get a grand total.
select
ReqNum as 'Req#',
LnItemNum as 'Line#',
lnqty as 'Qty',
LnDesc as 'Description',
lnprice as 'Price',
(LnQty*LnPrice) as 'Extension'
from tblreq,
tblLineItem
where tblreq.ReqNum = tblLineItem.LnReqNum and tblreq.ReqNum = 2006617
order by tblLineItem.LnItemNum
The query yields this result:
2006617 1 1 Keytronic 17.55 17.55
2006617 2 2 Intel STS 27.24 54.48
2006617 3 1 Intel SC 327.87 327.87
2006617 4 1 Sony 15.64 15.64
2006617 5 6 Kingsto 53.46 320.76
2006617 6 1 Window 142.63 142.63
2006617 7 1 Viewso 159.31 159.31
2006617 8 1 Viewso -20.00 -20
2006617 9 1 Intel S5 417.27 417.27
2006617 10 2 Intel 1216.01 2432.02
2006617 11 4 WD45 235.85 943.4
I am trying to sum the 'Extension' column to get a grand total.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jim,
It did not work as expected, but I figured out how to do it from your tutorial. By the way, one of the best tutorials I have seen on this subject. I look forward to going through it in more detail.
It did not work as expected, but I figured out how to do it from your tutorial. By the way, one of the best tutorials I have seen on this subject. I look forward to going through it in more detail.
Thanks for the split. Good luck with your project. -Jim
ASKER
Not exactly what I was looking for, but gets the desired result. I was trying to do it with a nested query, but there was something wrong in my syntax. I will keep trying to figure it out just to satisfy my curiosity.