How do I get the percentage value next to row in SQL Server? -
i have following query returns top 50 sum(detailtins)
records. need have column show percentage of sum(detailtins)
on records in table not first 50.
what need modify in following query?
also using sql server 2008 r2 cannot use over partition by
function.
with cte ( select id, name, city, state, sum(detailtins) sumtins feedrpts (rpttimeframe between @begindate , @enddate) , ((@value = 'tn' , state = 'tn') or (@value = 'oos' , state <> 'tn') or (@value = 'all')) group id, name, city, state ) select top 50 * cte order sumtins desc
you can introduce variable calculate total sum, , use variable calculate percentage:
declare @totaltins int select @totaltins = sum(detailtins) feedrpts (rpttimeframe between @begindate , @enddate) , ((@value = 'tn' , state = 'tn') or (@value = 'oos' , state <> 'tn') or (@value = 'all')) cte ( select id, name, city, state, sum(detailtins) sumtins feedrpts (rpttimeframe between @begindate , @enddate) , ((@value = 'tn' , state = 'tn') or (@value = 'oos' , state <> 'tn') or (@value = 'all')) group id, name, city, state ) select top 50 *, cast(sumtins float) / cast(@totaltins float) percentage cte order sumtins desc
Comments
Post a Comment