Solved

How to sum a calculated field in sql server

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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