December 14, 2010 at 3:03 am
Hi
Below one is my sample table 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
i need the result like below
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
Result calculation is
(Counts divided
sum of count (16+14+60+19+2)
Multiply 100)
formula
counts/sumofcount *100
Can any one please guide me through sample code.
December 14, 2010 at 3:24 am
Can you show us how you have you attempted to solve this problem yourself ?
December 14, 2010 at 3:28 am
Actually this is my actual query
Declare @Month int,@Year int
set @Month=12
SEt @Year=2010
;with cte
as
(
SELECT TOP 5 row_number() over(order by (select 0)) ID,dbo.Assets.AssetShortName,COUNT(*) AS Counts
FROM dbo.Proposals INNER JOIN dbo.Assets ON dbo.Proposals.AssetID=dbo.Assets.AssetID
WHERE
DATEPART(MM,dbo.Proposals.SubmissonDate)=@Month AND
DATEPART(YYYY,dbo.Proposals.SubmissonDate)=@Year
GROUP BY
dbo.Assets.AssetShortName
),cte1 as
(
SELECT ID,
AssetShortName,
O.Counts,
(SELECT sum(Counts)
FROM cte
WHERE ID <= O.ID) 'Total'
FROM cte O
),cte2
as
(
Select ID,AssetShortName,Counts, (Select MAX(total) total from cte1 ) TotalValue
from cte1
)
Select ID,AssetShortName,Counts,(cast(Counts as decimal) / cast(TotalValue as decimal)*100) Result from cte2
am getting exact result. but query length is too lengthy. so that i would like to optimize this query.
Can you please guide me.
December 14, 2010 at 6:55 am
There seem to bit a bit more going on in there :ermm:
But this should solve the 'simple' question you posed ...
select *,((counts*1.0) / sum(counts) over (partition by 1))*100.0
from @t
December 14, 2010 at 10:48 pm
Nice,Thanks dave.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply