determine percentage from aggregate values

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

Transact-SQL (2008)

determine percentage from aggregate values

Author

Topic

sness
Starting Member

7 Posts

Posted - 2011-05-18 : 15:41:18

Need help on something that is probably pretty basic. I have a view that returns values:

month, ID, count, met_kpi
2011-01-01, 100-04-05, 1, 0
2011-01-01, 100-04-06, 1, 1
2011-01-01, 100-04-07, 1, 0
2011-02-01, 100-04-10, 1, 1
2011-02-01, 100-04-08, 1, 0

Values for count will always be 1, and met_kpi will be 1 or zero.

I need to aggregate at the month so I end up with something like:

month, count, met_kpi, pct
2011-01-01, 3, 1, .33
2011-02-01, 2, 1, .5

A simple query of

select month, sum(count) as count, sum(met_kpi) as met_kpi
from table1
group by month

gets me most of the way there, and adding sum(met_kpi)/sum(count) as pct only returns a value of 0 on all rows.

I'm guessing I need some type of sub-query??

Appreciate any help.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-18 : 16:04:12

Use this ...

select col1,sum(cnt) Tcount, SUM(met_kpi) metKPI, convert(Numeric(10,2),SUM(met_kpi)/Convert(numeric(10,1),sum(cnt)))
from @tblPersons
Group by Col1

Cheers
MIK

sness
Starting Member

7 Posts

Posted - 2011-05-18 : 16:13:23

Perfect!!! thanks and if you can, why did this work? what does the CONVERT add?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-18 : 16:43:09

SQL server automatically rounds the calculations if columns are integer. In the given case sum results in integer which needed to be change into Decimal datatype in order to get the exact value. Run the following example and hope you'll get better explanation your selves :)

Select 1/3
Select 1/3.0

Cheers
MIK

sness
Starting Member

7 Posts

Posted - 2011-05-18 : 17:28:32

Okay, thanks again for the folow-up. Understood and very much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources