over() with count - 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)

over() with count

Author

Topic

mweels
Starting Member

1 Post

Posted - 2010-03-12 : 18:45:47

Hello everyone, I am hoping someone can help me with this.

I have the following query..

select
parent.description,
state.Name,
COUNT(*) as count

from
parent WITH (NOLOCK)
join
state WITH (NOLOCK) on state.id = parent.stateid
where isactive=1 and organizationid in (select organizationid from resourcerole WITH (NOLOCK))
group by
state.Name, Parent.Description
order by Parent.Description, state.name

Which returns :;

New Application Deployment 26
New Application FinalApplicationReview 2
New Application Live 537
New Application MerchantFillOut 45
New Application MerchantSignature 4
New Application Processing 1
New Application SalesReview 435
New Application Underwriting 10
New CloseAccount CloseMerchantRequest 1

I am trying to use the over() statement to find out what the percentage is using the first column based off the count.

The results should look like ...

New Application Deployment 26 2.478551001
New Application FinalApplicationReview 2 0.190657769
New Application Live 537 51.19161106
New Application MerchantFillOut 45 4.289799809
New Application MerchantSignature 4 0.381315539
New Application Processing 1 0.095328885
New Application SalesReview 434 41.37273594
New Application Underwriting 10 0.953288847
New CloseAccount Close Merchant Request 1 100

Any help would be appreciated, thank you in advance.

Marty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 19:09:51

try . .

count(*) * 100.0 / count(count(*)) over (partition by parent.description)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources