Solved

How to sum a calculated field in sql server

Posted on 2014-11-10
5
129 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
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

910 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

20 Experts available now in Live!

Get 1:1 Help Now