Last post Oct 17, 2013 10:51 AM by lewis886
Oct 16, 2013 03:33 PM|lewis886|LINK
I really wanted to try to figure this out on my own, but I figured I better give up and ask you guys before I smash my keyboard.
What I'm trying to do is to get a subquery to perform a calculation, and then sum the result. I have a Table of Shells, and then a table of Lines which are attached to those shells. I need it to multiply two columns from the lines table, and then add another
column, and then sum the results together for a total per shell. For Example.
CompNum,Line, RejQty, RejPrice, ExtraCosts
The Calculation should be (RejQty * RejPrice) + ExtraCosts and then that result should be summed to get a total for each Shell.
And I want the Query to give me the following: The Calculation for 5024 being --- ( ((50*1.24) + 50)=112 "Line-1" ) + ( (20*2.56)+200)=251.2 "Line-2" ) = 363.20
I am doing all of this in the SqlDataSource in the Asp.net page. Here is my latest version out of about 50 or so (which obviously doesn't work). I've tried making it a subquery, i've tried making it a Join, and I just can't get it. I can get it to SUM each
of those columns individually, but I can't get it to do the calculation and then SUM them.
SELECT [CompNum]=Shell.[CompNum], Shell.[Customer], Shell.[CSR],
((SELECT SUM(Line.[ExtraCosts]) + (Line.[RejQty] * Line.[RejUnitPrice])) AS Amount FROM [Lines] WHERE Line.[CompNum] = Shell.[CompNum]) FROM [Shells] AS Shell GROUP BY Shell.[CompNum], Shell.[Customer],"
EDIT: Removed some of the non-relevant columns in the query.
Oct 17, 2013 07:12 AM|Happy Chen - MSFT|LINK
I would suggest you try the workaround below:
select Shell.[CompNum],Shell.[Customer],sum(Amount) Amount from
(Line.[ExtraCosts]) + (Line.[RejQty] * Line.RejPrice)
AS Amount FROM [Lines] as Line
) m, [dbo].[Shells] AS Shell
group by Shell.[CompNum],Shell.[Customer]
i hope it helps you.
Oct 17, 2013 10:51 AM|lewis886|LINK
Thanks. I had been working on that for a couple days and it was driving me nuts. I really appreciate you helping me out. I just had to add the ISNULL function on the ExtraCosts field (since that field doesn't always have data) and it worked perfectly.