sql query optimisation - 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)

sql query optimisation

Author

Topic

zion99
Posting Yak Master

141 Posts

Posted - 2009-07-20 : 01:12:38

[code]
declare @tbl1 ta
ble
(TBid int
,cType smallint
,cPercent decimal(3,2)
)

insert into @tbl1(TBid, cType)
select 11,1 union all
select 12,2 union all
select 11,3 union all
select 11,1 union all
select 13,2 union all
select 11,3 union all
select 11,1 union all
select 13,2 union all
select 12,2 union all
select 11,3 union all
select 12,1 union all
select 12,2 union all
select 12,3 union all
select 14,1 union all
select 14,2

select * from @tbl1
[/code]

formula for calculating cPercent:

get the count of all TBid having cType = 1 group by TBid

DIVIDE BY

get the count of all TBid group by TBid

This will give me cPercent.
I am thinking of creating 2 more columns to get the above details and then do a divide
to get the percentage and update the cPercent Column

is there a better way of doing this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 01:27:52

[code]
select t1.TBid, t1.cType, cPercent = c.cnt * 100.0 / count(*) over (partition by t1.TBid)
from @tbl1 t1
inner join
(
select TBid, cnt = count(*)
from @tbl1
where cType = 1
group by TBid
) c on t1.TBid = c.TBid
[/code]

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

zion99
Posting Yak Master

141 Posts

Posted - 2009-07-20 : 01:32:21

thanks khtan :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources