Solved

How to sum a calculated field in sql server

Posted on 2014-11-10
5
134 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:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40433848
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
ID: 40435223
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
ID: 40437337
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
ID: 40438072
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
ID: 40438116
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.

Question has a verified solution.

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

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

830 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