How to use SUM aggregate function

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 2005 Forums

Transact-SQL (2005)

How to use SUM aggregate function

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-11 : 05:28:15

I have created the following code:

SELECT BGT_YEAR, BGT_MONTH, BGT_BGC_CODE, SUM(BGT_AMOUNT) AS Expr1
FROM BUDGET
WHERE BGT_FINANCIAL_YEAR = 2010
GROUP BY BGT_BGC_CODE, BGT_YEAR, BGT_MONTH

Now I have another table that I have created with some parameters as Columns and I do the following to multiply these parameteres with lines from BUDGET table :

SELECT a.BGT_YEAR, a.BGT_MONTH, a.BGT_BGC_CODE (a.BGT_AMOUNT * b.CFL_VAT_PERCENTAGE) AS Multi
FROM BUDGET a
INNER JOIN CASH_FLOW b
ON a.BGT_BGC_CODE = b.CFL_BGC_CODE

Is there any way I can aggregate(sum) the Multi column as I did in the previous code?
Thank you very much!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 05:32:03

Yes, you can use your second select as derived table and do your stuff on this.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 05:34:25

SELECT a.BGT_YEAR, a.BGT_MONTH, a.BGT_BGC_CODE, SUM(a.BGT_AMOUNT * b.CFL_VAT_PERCENTAGE) AS Multi
FROM BUDGET a
INNER JOIN CASH_FLOW b
ON a.BGT_BGC_CODE = b.CFL_BGC_CODE
GROUP BY a.BGT_YEAR, a.BGT_MONTH, a.BGT_BGC_CODE

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 05:41:58

OK, you can do it without derived table

No, you're never too old to Yak'n'Roll if you're too young to die.

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-11 : 05:44:10

I followed your suggestion and it seems to be working.
Thank you very much!

skiabox
Posting Yak Master

169 Posts

Posted - 2009-09-11 : 05:48:00

quote:
Originally posted by madhivanan

SELECT a.BGT_YEAR, a.BGT_MONTH, a.BGT_BGC_CODE, SUM(a.BGT_AMOUNT * b.CFL_VAT_PERCENTAGE) AS Multi
FROM BUDGET a
INNER JOIN CASH_FLOW b
ON a.BGT_BGC_CODE = b.CFL_BGC_CODE
GROUP BY a.BGT_YEAR, a.BGT_MONTH, a.BGT_BGC_CODE

Madhivanan

Failing to plan is Planning to fail

I used the more verbose

SELECT BGT_YEAR, BGT_MONTH, BGT_BGC_CODE, SUM(Multi) AS Expr1
FROM

(SELECT a.BGT_YEAR, a.BGT_MONTH, a.BGT_BGC_CODE, (a.BGT_AMOUNT * b.CFL_VAT_PERCENTAGE) AS Multi, a.BGT_FINANCIAL_YEAR
FROM BUDGET a
INNER JOIN CASH_FLOW b
ON a.BGT_BGC_CODE = b.CFL_BGC_CODE) AS t

WHERE BGT_FINANCIAL_YEAR=2010
GROUP BY BGT_BGC_CODE, BGT_YEAR, BGT_MONTH

...because t replaces exactly one table in a stored procedure that I use.
Thank you all for your help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 06:53:42

Haha - derived table

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources