Aggregate fnction - 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

General SQL Server Forums

New to SQL Server Programming

Aggregate fnction

Author

Topic

nkpriya
Starting Member

34 Posts

Posted - 2010-01-04 : 17:27:36

Hi,
I have a view with columns -
Month,CostCenter,AccountName,Actual,Budget

I need to calculate YTD calculation for Actual and Budget for each month.

For eg:
2009-01|90293|Plan1|$190|$100
2009-01|02020|Plan1|$120|$102
2009-02|02102|Plam2|$192|$200
2009-02|10292|Plan2|$101|$202

Now, I need to calculate YTD calculation for Actual as Sum(Actual for 2009-01)+Sum(Actual for 2009-02) and then divided by number of months.Here the result for Actual should be $302. How Can I achieve this? Please help.

Thanks
Krishna

singularity
Posting Yak Master

153 Posts

Posted - 2010-01-04 : 19:23:47

[code]
select sum(actual) / count(distinct [month])
from yourview
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 03:16:51

quote:
Originally posted by singularity

select sum(actual)* 1.0 / count(distinct [month])
from yourview

If you need decimal part also

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources