locked
SubQuery Calculations RRS feed

  • Question

  • User1122680954 posted

    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.

    Shells table:

    CompNum, Customer

    5024, Customer1

    5025, Customer2

    Lines table:

    CompNum,Line, RejQty, RejPrice, ExtraCosts

    5024,1,50,1.24,50

    5024,2,20,2.56,200

    5025,1,350,2.34,0

    5025,2,120,5.90,0

    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

    5024,Customer1,363.20

    5025,Customer2,1527




    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.

    Wednesday, October 16, 2013 3:33 PM

Answers

  • User-933407369 posted

    hi lewis886,

    I would suggest you try the workaround below:

      select Shell.[CompNum],Shell.[Customer],sum(Amount) Amount from
    			  (
    			  SELECT 	[CompNum],		
    			(Line.[ExtraCosts]) + (Line.[RejQty] * Line.RejPrice)
    					  AS Amount FROM  [Lines] as Line
    			 ) m, [dbo].[Shells] AS Shell 
    			 where m.CompNum=Shell.CompNum
    			 group by Shell.[CompNum],Shell.[Customer]

    i hope it helps you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 17, 2013 7:12 AM

All replies

  • User-933407369 posted

    hi lewis886,

    I would suggest you try the workaround below:

      select Shell.[CompNum],Shell.[Customer],sum(Amount) Amount from
    			  (
    			  SELECT 	[CompNum],		
    			(Line.[ExtraCosts]) + (Line.[RejQty] * Line.RejPrice)
    					  AS Amount FROM  [Lines] as Line
    			 ) m, [dbo].[Shells] AS Shell 
    			 where m.CompNum=Shell.CompNum
    			 group by Shell.[CompNum],Shell.[Customer]

    i hope it helps you.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 17, 2013 7:12 AM
  • User1122680954 posted

    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.  Thanks again Laughing

    Thursday, October 17, 2013 10:51 AM