Calculate a percentage in a group

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)

Calculate a percentage in a group

Author

Topic

Bill Humphrey
Starting Member

4 Posts

Posted - 2009-03-25 : 09:55:31

Hi

I would like to create a percentage in a group by date:

01/03/2009 Durham 55
01/03/2009 North 45
01/03/2009 Total 100
02/03/2009 Durham 26
02/03/2009 North 24
02/03/2009 Total 50

I need to use the total for each group to calculate the percentage so the first line would be:

55 / 100 * 100 = 55.0%

The output should be for all rows:

01/03/2009 Durham 55 55%
01/03/2009 North 45 45%
01/03/2009 Total 100 100%
02/03/2009 Durham 26 52%
02/03/2009 North 24 48%
02/03/2009 Total 50 100%

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-25 : 10:11:59

[code]create table #s(dt datetime, region varchar(100), score float)

insert into #s
select
*
from
(
select '01/03/2009' dt, 'Durham' region,55 score union all
select '01/03/2009', 'North' ,45 union all
select '01/03/2009', 'Total' ,100 union all
select '02/03/2009', 'Durham' ,26 union all
select '02/03/2009', 'North' ,24 union all
select '02/03/2009', 'Total', 50
)abc

select a.dt,
a.region,
(a.score
/
(select
score
from #s
where
year(a.dt)=year(dt)
and month(a.dt)=month(dt)
and region='total')
)
*100E
from
#s a[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 13:43:58

sql 2005 specific


SELECT dt,region,score,score * 100.0/sum(case when region='total' then score else 0 end) over (partition by dt) AS percentage
FROM table

Bill Humphrey
Starting Member

4 Posts

Posted - 2009-03-26 : 06:06:21

Thanks Gents

That was top draw

Kind Regards

Bill Humphrey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:26:48

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources