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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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.
0
tmalmond
Asked:
tmalmond
  • 2
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
If you just want a quick-and-dirty total from an ad hoc query, you can do 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
 compute sum((LnQty*LnPrice))
0
 
tmalmondAuthor Commented:
Thanks Scott,

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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Assuming that there are no duplicates for all the non-sum columns (otherwise you'd have to remove at least lnqry and lnprice from the SELECT clause), give this a whirl..
SELECT 
      ReqNum as 'Req#',
      LnItemNum as 'Line#',
      lnqty as 'Qty',
      LnDesc as 'Description',
      lnprice as 'Price',
      SUM(LnQty*LnPrice) as 'Extension'
FROM tblreq,
	JOIN tblLineItem ON tblreq.ReqNum = tblLineItem.LnReqNum and tblreq.ReqNum = 2006617
GROUP BY ReqNum, LnItemNum, lnqty, LnDesc, lnprice
ORDER BY tblLineItem.LnItemNum

Open in new window

If you'd like some more reading on GROUP BY I have an image and code-heavy tutorial out there called SQL Server GROUP BY Solutions
0
 
tmalmondAuthor Commented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now