CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012
Mohsin Mushtaq
Mohsin Mushtaq

Reputation: 133

Use a formula on SQL Server column values and store it in new column

I'm trying to use a formula on a column, what I want is I have a value in a column I want to multiply it by (100 / SumofColumnValues) what I succeeded till now is get the Sum of Count

select 
    Count(*) as ResponseCount,
    PropertyValue As Answer 
from 
    table 
where 
    Questionid = 42 and formid = 1 
group by 
    propertyvalue

This give something like this

ResponseCount  Answers
     34        One
     100       Two

and then using CTE:

With Temp As (
    select 
        PropertyValue As Answers,
        Count(*) As ResponseCount 
    from 
        questionerdetail 
    where 
        Questionid = 42 and formid = 1 
    group by 
        PropertyValue
)
select Sum(ResponseCount) As Total 
from Temp

I get 

Total
 134

What I need is 

ReponseCount  Answer  ResponsePercentage
     34       One       25.37       TheFormula will be 34*(100/134)- (134 is the total sum of responsecount)
     100      Two       74.62       TheFormula Will be 100*(100/134)

Upvotes: 0

Views: 169

Answers (2)

Arve Hansen
Arve Hansen

Reputation: 1008

Or if you want to use analytical functions instead of sub-select:

    select 
    Count(*) as ResponseCount,
    PropertyValue As Answer ,
 convert ( dec(28,2) ,Count(*))*100/(sum(count(*)) over ()) as ResponsePercentage
from 
    table 
where 
    Questionid = 42 and formid = 1 
group by 
    propertyvalue

(The convert function is SQL Server syntax)

Upvotes: 2

Habeeb
Habeeb

Reputation: 1040

select 
    Count(*) as ResponseCount,
    PropertyValue As Answer ,
 Count(*)*100/(select count(*) from table ) as ResponsePercentage
from 
    table 
where 
    Questionid = 42 and formid = 1 
group by 
    propertyvalue

Upvotes: 1

Related Questions