Calculating the sum of sums - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating the sum of sums

Author

Topic

hwilliford
Starting Member

3 Posts

Posted - 2004-10-14 : 22:52:31

Scenario:

I need to calculate the sum of two previously summed columns.

I know how to do this using a temporary table, but how can I do it without a temp table.

Spent hours researching and trying all combinations of sql, but no luck.

Appreciate the exact syntax.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-14 : 23:02:53

SELECT SumColumn1 + SumColumn2 AS SumOfCol1n2
FROM (SELECT SUM(Column1) AS SumColumn1, SUM(Column2) AS SumColumn2 FROM Table1) t

Maybe you should post your query.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-15 : 08:56:03

or maybe:

select sum(a) as SumOfA, sum(b) as SumOfB, sum(a+b) as SumOfBoth
from yourtable

Remember you have two options -- get the totals for each of the values, and then add the totals together. Or, add up the two values and get the sum of THAT.

Don't you think a little more info would be helpful for us?

- Jeff

hwilliford
Starting Member

3 Posts

Posted - 2004-10-15 : 12:31:43

Thanks, the example worked fine (once I move the alias to the right location).

Really Appreciate your help.

slboytoy
Starting Member

30 Posts

Posted - 2004-10-21 : 11:41:59

I have a similar problem that I am trying to work out. I'll even post my code.


SELECT     dbo.QuoteHDR.PlantNumber, dbo.QuoteHDR.QuoteNumber, dbo.QuoteHDR.EffectiveStartDate, dbo.QuoteHDR.CustomerNumber, 
                      dbo.QuoteHDR.CustomerName, (CONVERT(float, dbo.QuoteITM.LineItemNetUnitPrice) + SUM(CONVERT(float, ISNULL(dbo.QuoteITX.Adder, 0)))) 
                      * dbo.QuoteITM.LineItemQuantity * dbo.QuoteITM.DiscountMultiplier AS NetQuoteValue, dbo.QuoteHDR.YourInquiry, 
                      MAX(dbo.QuoteHST.QuoteHistoryDate) AS Expr1
FROM         dbo.QuoteHST RIGHT OUTER JOIN
                      dbo.QuoteHDR INNER JOIN
                      dbo.QuoteITM ON dbo.QuoteHDR.QuoteNumber = dbo.QuoteITM.QuoteNumber ON 
                      dbo.QuoteHST.QuoteNumber = dbo.QuoteHDR.QuoteNumber LEFT OUTER JOIN
                      dbo.QuoteITX ON dbo.QuoteITM.LineItem = dbo.QuoteITX.Item AND dbo.QuoteITM.QuoteNumber = dbo.QuoteITX.QuoteNumber
WHERE     (dbo.QuoteHDR.PlantNumber = '18')
GROUP BY dbo.QuoteHDR.PlantNumber, dbo.QuoteHDR.QuoteNumber, dbo.QuoteHDR.EffectiveStartDate, dbo.QuoteHDR.CustomerNumber, 
                      dbo.QuoteHDR.CustomerName, dbo.QuoteHDR.YourInquiry, dbo.QuoteITM.LineItem, dbo.QuoteITM.DiscountMultiplier, dbo.QuoteITM.LineItemQuantity, 
                      dbo.QuoteITM.LineItemNetUnitPrice
HAVING      (dbo.QuoteHDR.QuoteNumber = '000525T508')

THe issue I am having is with the part where I am doing a Sum, I want to sum the total of that. I'll show you want I mean, right now I get..


	Plant	Number	        Date	        NumberC	NameC	                Price	Guy          	        Date2
	18	000525T508   	5/25/2000	733162 	PRO-LTD         	47.35125	RFQ#142457          	5/29/2000
	18	000525T508   	5/25/2000	733162 	PRO-LTD         	434.2104	RFQ#142457          	5/29/2000
	18	000525T508   	5/25/2000	733162 	PRO-LTD         	13.9725	RFQ#142457          	5/29/2000
	18	000525T508   	5/25/2000	733162 	PRO-LTD         	47.35125	RFQ#142457          	5/29/2000
	18	000525T508   	5/25/2000	733162 	PRO-LTD         	434.2104	RFQ#142457          	5/29/2000
	18	000525T508   	5/25/2000	733162 	PRO-LTD         	13.9725	RFQ#142457          	5/29/2000

I would like to sum up the 'Price' Column. I can do it with a #temp table, but having a little problems trying to do it all at once.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 12:43:35

slboytoy,
have you thought of using COMPUTE clause ?

/rockmoose

slboytoy
Starting Member

30 Posts

Posted - 2004-10-21 : 12:47:21

I've never come across that. I'm looking it up now, to see what its all about.

That didn't get me too far. It does give me the answer, but I can't insert that answer into the temp table. And the temp table, still contains the 6 or so records.

I just want the one record in there, with the sum of all the prices

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-10-21 : 15:35:12

what about a WITH ROLLUP??


SELECT     dbo.QuoteHDR.PlantNumber, dbo.QuoteHDR.QuoteNumber, dbo.QuoteHDR.EffectiveStartDate, dbo.QuoteHDR.CustomerNumber, 
                      dbo.QuoteHDR.CustomerName, (CONVERT(float, dbo.QuoteITM.LineItemNetUnitPrice) + SUM(CONVERT(float, ISNULL(dbo.QuoteITX.Adder, 0)))) 
                      * dbo.QuoteITM.LineItemQuantity * dbo.QuoteITM.DiscountMultiplier AS NetQuoteValue, dbo.QuoteHDR.YourInquiry, 
                      MAX(dbo.QuoteHST.QuoteHistoryDate) AS Expr1
FROM         dbo.QuoteHST RIGHT OUTER JOIN
                      dbo.QuoteHDR INNER JOIN
                      dbo.QuoteITM ON dbo.QuoteHDR.QuoteNumber = dbo.QuoteITM.QuoteNumber ON 
                      dbo.QuoteHST.QuoteNumber = dbo.QuoteHDR.QuoteNumber LEFT OUTER JOIN
                      dbo.QuoteITX ON dbo.QuoteITM.LineItem = dbo.QuoteITX.Item AND dbo.QuoteITM.QuoteNumber = dbo.QuoteITX.QuoteNumber
WHERE     (dbo.QuoteHDR.PlantNumber = '18')
GROUP BY dbo.QuoteHDR.PlantNumber, dbo.QuoteHDR.QuoteNumber, dbo.QuoteHDR.EffectiveStartDate, dbo.QuoteHDR.CustomerNumber, 
                      dbo.QuoteHDR.CustomerName, dbo.QuoteHDR.YourInquiry, dbo.QuoteITM.LineItem, dbo.QuoteITM.DiscountMultiplier, dbo.QuoteITM.LineItemQuantity, 
                      dbo.QuoteITM.LineItemNetUnitPrice

WITH ROLLUP  'This is the line I added  

HAVING      (dbo.QuoteHDR.QuoteNumber = '000525T508')

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

slboytoy
Starting Member

30 Posts

Posted - 2004-10-21 : 17:39:39

No Rollup doesn't work write.

All I want to do is sum(Net+ sum(Adder) * Qty * Disc)

slboytoy
Starting Member

30 Posts

Posted - 2004-10-21 : 18:00:22

So I have my code like this so far, which works.



select HDR.PlantNumber
     , HDR.QuoteNumber
     , HDR.EffectiveStartDate
     , HDR.CustomerNumber
     , HDR.CustomerName
     , (Select sum(NetQuoteValue) from (
       select (convert(float, ITM.LineItemNetUnitPrice) 
             + sum(convert(float, isnull(ITX.Adder, 0)))) 
              * ITM.LineItemQuantity 
              * ITM.DiscountMultiplier as NetQuoteValue
        from dbo.QuoteITX as ITX
RIGHT OUTER 
 JOIN dbo.QuoteHDR as HDR
INNER 
 JOIN dbo.QuoteITM as ITM
   ON HDR.QuoteNumber = ITM.QuoteNumber
   ON ITX.Item = ITM.LineItem and ITX.QuoteNumber = ITM.QuoteNumber  
 where HDR.PlantNumber = '18'
   and HDR.QuoteNumber = '040512T401R01'
       group 
           by ITM.DiscountMultiplier
            , ITM.LineItemQuantity
            , ITM.LineItemNetUnitPrice 
       ) as DT) as NetQuoteValue 
     , HDR.YourInquiry
from dbo.QuoteITX as ITX
RIGHT OUTER 
 JOIN dbo.QuoteHDR as HDR
INNER 
 JOIN dbo.QuoteITM as ITM
   ON HDR.QuoteNumber = ITM.QuoteNumber
   ON  ITX.QuoteNumber = ITM.QuoteNumber  
 where HDR.PlantNumber = '18'
   and HDR.QuoteNumber = '040512T401R01'
group 
    by HDR.PlantNumber
     , HDR.QuoteNumber
     , HDR.EffectiveStartDate
     , HDR.CustomerNumber
     , HDR.CustomerName
     , HDR.YourInquiry

Is there a way to link the subquery to the main query? So for the main query, I can say I want this quote and this quote. And the subquery will link by the quote numbers. (instead of me actually putting in the actually quotenumber)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-21 : 19:01:29

Derived table ?
SELECT SUM(Price) FROM
(
select ... from ...
) AS Horrenduous_Sql_Query

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources