HOw to calculate the average - 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 2005 Forums

Transact-SQL (2005)

HOw to calculate the average

Author

Topic

urzsuresh
Starting Member

30 Posts

Posted - 2010-12-14 : 05:07:22

Hello friends,
Below One is sample structure.


Declare @t table
(
        id int,
        name nvarchar(50),
        counts int
)
Insert into @t
Select 1,'AA',16 Union all
Select 2,'BB',14 Union all
Select 3,'cc',60 Union all
Select 4,'dd',19 Union all
Select 5,'ee',2

select * from @t

Expecting output

id name counts Result
----------- ---------- -----------
1 AA 16 14.2857142857142857
2 BB 14 12.5000000000000000
3 cc 60 53.5714285714285714
4 dd 19 17.8571428571428571
5 ee 2 1.7857142857142857

Formula for calculating result is

counts/sumofcount *100

sumofcount=(16+14+60+19+2)

Can any please guide me through sample code

Suri

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-14 : 05:28:11

select *, 100E * counts / sum(counts) over ()
from @t

N 56°04'39.26"
E 12°55'05.63"