CODEWITHSUNDEEP
sqlsql-servert-sqlpercentage
Jason M. Kluczyk
Jason M. Kluczyk

Reputation: 11

convert three columns into percentages for the row

I don't know how to put this best in words, but below is my data and i'm trying to build a query that will output each column as a percent of the three items of cash, planpay, govt

column data:

1

output to be like:

2

I have tried this

select str_nbr ,(sum(cast(planpay as float) / (sum(cast (planpay as float) + sum (cast (cash as float) + sum (cast (govt as float))) 

from Insurance where str_nbr in ( '15308') group by str_nbr

Upvotes: 0

Views: 382

Answers (2)

underscore_d
underscore_d

Reputation: 6792

Just do the same logic as in Excel, but in T-SQL. Avoid repetition with a CTE

with cte as (
  select
    str_nbr,
    CASH,
    PLANPAY,
    GOVT,
    CASH + PLANPAY + GOVT as total
  from Insurance
)
select
  str_nbr as store,
  100.0 * CASH    / total as cash,
  100.0 * PLANPAY / total as planpay,
  100.0 * GOVT    / total as govt
from cte;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can just use division. If you don't want to repeat the calculation, use apply:

select t.str_nbr,
       t.cash * 100.0 / v.total as cash_pct,
       t.planpay * 100.0 / v.total as planpay_pct,
       t.govt * 100.0 / v.total as govt_pct
from t cross apply
     (values (cash + planpay + govt)) v(total)

Note that SQL Server does integer division. So if the values are integers, then 1 / 2 is 0 rather than 0.5. The * 100.0 fixes this.

Upvotes: 1

Related Questions