Solved

How to sum a calculated field in sql server

Posted on 2014-11-10
5
122 Views
Last Modified: 2014-11-12
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
Comment
Question by:tmalmond
  • 2
  • 2
5 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
Comment Utility
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
 

Author Comment

by:tmalmond
Comment Utility
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
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
 

Author Comment

by:tmalmond
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now