How to calculate percentage from the rows returned

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 percentage from the rows returned

Author

Topic

gauravsh27
Starting Member

2 Posts

Posted - 2009-04-13 : 14:29:09

Hi! everyone

I got this requirement and not able to find a suitable method to complete this...

My table Table1 has three columns

ID - Primary Key
CentreId - Foreign key of some other table
NAME
Percentage

Records are like this in the table

1 1 abc NULL
2 2 lmn NULL
3 2 opq NULL
4 3 xyz1 NULL
5 3 xyz2 NULL
6 3 xyz3 NULL

I want to show output like this

1 1 able 100%
2 2 lmn 50%
3 2 opq 50%
4 3 xyz1 33%
5 3 xyz2 33%
6 3 xyz3 33%

Suppose if there are 3 records of a particular foreign key i shd gv each record percentage as 33%
and if there are 4 records of a particular foreign key i shd gv each record percentage as 25% and so on

I want to write this with a single select statement

any help would be highly appreciated

Thanks in advance...

and suppose Table1 Contains sm records with percentage already defined my query wud leave those records the way they are

suppose the records are like this

1 10 adads NULL
2 20 sadasd 50%
2 20 sdfsdf 50%

Output shd be

1 10 adads 100%
2 20 sadasd 50%
2 20 sdfsdf 50%

Looking to hear from u all...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-13 : 14:37:25

This??

declare @t table (priid int,forid int, nme varchar(10),perc int)
insert @t
select 1,1,'abc',null union all
select 2,2,'def',null union all
select 3,2,'ijk',null union all
select 4,3,'lmn',null union all
select 5,3,'opq',null union all
select 6,3,'rst',null union all
select 7,4,'tst',90

update a set a.perc = 100 / b.forcnt
from @t a inner join (select forid,count(forid) as forcnt from @t group by forid) b
on a.forid = b.forid and a.perc is null

select * from @t

Result
-------
priid forid nme perc
----------- ----------- ---------- -----------
1 1 abc 100
2 2 def 50
3 2 ijk 50
4 3 lmn 33
5 3 opq 33
6 3 rst 33
7 4 tst 90

gauravsh27
Starting Member

2 Posts

Posted - 2009-04-13 : 15:06:31

Thanks vijay... Its Rocking!!!

quote:
Originally posted by vijayisonly

This??

declare @t table (priid int,forid int, nme varchar(10),perc int)
insert @t
select 1,1,'abc',null union all
select 2,2,'def',null union all
select 3,2,'ijk',null union all
select 4,3,'lmn',null union all
select 5,3,'opq',null union all
select 6,3,'rst',null union all
select 7,4,'tst',90

update a set a.perc = 100 / b.forcnt
from @t a inner join (select forid,count(forid) as forcnt from @t group by forid) b
on a.forid = b.forid and a.perc is null

select * from @t

Result
-------
priid forid nme perc
----------- ----------- ---------- -----------
1 1 abc 100
2 2 def 50
3 2 ijk 50
4 3 lmn 33
5 3 opq 33
6 3 rst 33
7 4 tst 90

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-13 : 15:25:55

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources