can any one please tell how to calculate percentage depend on this two column?

Question

Post reply

can any one please tell how to calculate percentage depend on this two column?

Sivaganesh Tamilvendhan

Hall of Fame

Points: 3498
More actions
November 3, 2012 at 2:19 am

#264235

for example there iam having table @counttable table in i want to caluculate percentage
by this to two column countvalue ,Answer eg; percentage= (,countvalue / Answer)*100
this is the table
DECLARE @counttable table
(
countid int identity(1,1),
contactid int,
countvalue int,
TotalRespondent int,
Answer int
)
insert into @counttable
select 1,1,3,2 union all
select 1,2,3,2 union all
select 1,1,3,2 union all
select 1,1,3,2 union all
select 2,1,3,1 union all
select 2,1,3,1 union all
select 2,1,3,1 union all
select 3,0,3,0 union all
select 3,0,3,0 union all
select 3,0,3,0
i tried three different ways
FIRST TYPE
select
countid
,contactid
,countvalue
,TotalRespondent
, Answer
, COALESCE(countvalue/NULLIF(Answer,0), 0) as 'QuestionPercentage'
from @counttable
order by contactid
SECOND TYPE
SELECT
countid
,contactid
,countvalue
,TotalRespondent
, Answer
,countvalue / (Answer-ABS(SIGN(Answer))+1) as 'QuestionPercentage'
from
@counttable
order by contactid
IN THE THIRD TYPE LIKE THIS
select
countid
,contactid
,countvalue
,TotalRespondent
, Answer
,(CAST( ((CONVERT(DECIMAL(38,2),countvalue) /cast((Answer)as float))*100)as decimal(10,2)))as QuestionPercentage
from @counttable
order by contactid
WITH ERROR LIKE THIS
Msg 8134, Level 16, State 1, Line 21
Divide by zero error encountered.
iam trying to receive output like this
countidcontactid countvalue TotalRespondentAnswerQuestionPercentage
1 1 1 3 2 50.00
2 1 2 3 2 100.00
3 11 3 2 50.00
4 11 3 250.00
5 21 3 1100.00
6 21 3 1100.00
7 21 3 1100.00
8 30 3 00.00
9 30 3 00.00
10 30 3 00.00
can any one plz try to solve my problem

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

KcV SSCommitted Points: 1714 More actions · Answer 1

Hi sivaji,

try this :

select *, case countvalue when 0 then 0 else floor(((countvalue*1.0)/(answer*1.0))*100) end from @counttable order by countID

let me know result

kunal.

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 2

hi KcV

it was working good when count value is 0 and answer value is 0

but it make error when it was like this

DECLARE @counttable table

(

countid int identity(1,1),

contactid int,

countvalue int,

TotalRespondent int,

Answer int

)

insert into @counttable

select 3,0,3,0 union all

select 3,1,3,0 union all

select 3,0,3,1

select *, QuestionPercentage=case countvalue when 0 then 0 else floor(((countvalue*1.0)/(answer*1.0))*100 )

end from @counttable order by countID

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

sivajii (11/3/2012)
hi KcV
it was working good when count value is 0 and answer value is 0
but it make error when it was like this
DECLARE @counttable table
(
countid int identity(1,1),
contactid int,
countvalue int,
TotalRespondent int,
Answer int
)
insert into @counttable
select 3,0,3,0 union all
select 3,1,3,0 union all
select 3,0,3,1
select *, QuestionPercentage=case countvalue when 0 then 0 else floor(((countvalue*1.0)/(answer*1.0))*100 )
end from @counttable order by countID

So look at the problem and fix it. The only thing that can give you the error you're getting is when "answer" is zero. Look at the problem and decide what you want the aswer to be if "answer = 0" and then tweak the formula to make the error go away.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)