SQL casting a percentage

Hi,

I have the following stored procedure that doesn't work:

declare @total decimal(10,4)
set nocount on
select @total = count(*)
from DailyPollAnswers
where PollID = @PollID

Select Answer1, cast (cast(100.00 * (Select COUNT(1) From DailyPollAnswers Where DailyPoll.PollID = DailyPollAnswers.PollID)) / @total as int) as varchar(10)) + '%' Percentage
From DailyPoll
Where PollID = @PollID
UNION
Select Answer2, cast (cast(100.00 * (Select COUNT(1) From DailyPollAnswers Where DailyPoll.PollID = DailyPollAnswers.PollID)) / @total as int) as varchar(10)) + '%' Percentage
From DailyPoll
Where PollID = @PollID
UNION
Select Answer3, cast (cast(100.00 * (Select COUNT(1) From DailyPollAnswers Where DailyPoll.PollID = DailyPollAnswers.PollID)) / @total as int) as varchar(10)) + '%' Percentage
From DailyPoll
Where PollID = @PollID


The part where I want to get the percentage doesn't work.  I have a table called DailyPoll which has PollID, Question, Answer1, Answer2, Answer3.  Then there is a table DailyPollAnswers which stores each user's answer.  I want the stored procedure to return each answer's text, percentage of the final count, and number of votes (even if it's 0).

for example, it should look like this:

Answer   Percentage   Count
John S   70%   7
Tom Q   30%   3
Jim T   0%   0


thanks a million in advance.
attipaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
Hi, what are the columns in DailyPollAnswers please?
0
attipaAuthor Commented:
PollID   Answer   DateAdded
0
cyberkiwiCommented:
For each part, it will be something like this

Select Answer1, cast (cast(100.00 * isnull(Select COUNT(1) From DailyPollAnswers Where DailyPoll.PollID = DailyPollAnswers.PollID

AND DailyPollAnswers.Answer = X -- this part missing

),0) / @total as int) as varchar(10)) + '%' Percentage

The Isnull makes null into 0's
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

attipaAuthor Commented:
so i did this, but it still came back with a syntax error:

Select Answer1, cast (cast(100.00 * isnull(Select COUNT(1) From DailyPollAnswers Where DailyPoll.PollID = DailyPollAnswers.PollID
AND DailyPollAnswers.Answer = DailyPoll.Answer1
),0) / @total as int) as varchar(10)) + '%' Percentage
0
attipaAuthor Commented:
the syntax error is this:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'.

Line 10 is the line 1 in the example and line 12 is line 3
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Try this modified code:
Select Answer1, 
(Select case when COUNT(1) is null or count(1) = 0 or @total = 0 then 0
else cast(count(1) * 100 / @total as varchar(10)) + '%' end
From DailyPollAnswers 
Where DailyPoll.PollID = DailyPollAnswers.PollID
AND DailyPollAnswers.Answer = DailyPoll.Answer1) Percentage
FROM DailyPoll

Open in new window

0
attipaAuthor Commented:
almost there....now, it gave me this error:

Arithmetic overflow error converting numeric to data type varchar.

how can i fix that?
0
attipaAuthor Commented:
to help you out further...here is the complete stored procedure:


declare @PollID int
set @pollid = '100'

declare @total decimal(10,4)
set nocount on
select @total = count(*)
from DailyPollAnswers
where PollID = @PollID

Select Answer1,  
(Select case when COUNT(1) is null or count(1) = 0 or @total = 0 then 0
else cast(count(1) * 100 / @total as varchar(10)) + '%' end
From DailyPollAnswers  
Where DailyPoll.PollID = DailyPollAnswers.PollID
AND DailyPollAnswers.Answer = DailyPoll.Answer1) Percentage
FROM DailyPoll
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should work:
else change
else cast(count(1) * 100 / @total as varchar(15)) + '%'
to
else cast(cast(count(1) * 100 / @total as int) as varchar(10)) + '%'
declare @PollID int
set @pollid = '100'

declare @total decimal(10,4)
set nocount on 
select @total = count(*)
from DailyPollAnswers
where PollID = @PollID

Select Answer1,  
(Select case when COUNT(1) is null or count(1) = 0 or @total = 0 then 0 
else cast(count(1) * 100 / @total as varchar(15)) + '%' end 
From DailyPollAnswers  
Where DailyPoll.PollID = DailyPollAnswers.PollID 
AND DailyPollAnswers.Answer = DailyPoll.Answer1) Percentage 
FROM DailyPoll

Open in new window

0
attipaAuthor Commented:
now the error is: Conversion failed when converting the varchar value '50.00000000000%' to data type int.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Small Mistake:
declare @PollID int 
set @pollid = '100' 
 
declare @total decimal(10,4) 
set nocount on  
select @total = count(*) 
from DailyPollAnswers 
where PollID = @PollID 
 
Select Answer1,   
(Select case when COUNT(1) is null or count(1) = 0 or @total = 0 then '0'  
else cast(count(1) * 100 / @total as varchar(15)) + '%' end  
From DailyPollAnswers   
Where DailyPoll.PollID = DailyPollAnswers.PollID  
AND DailyPollAnswers.Answer = DailyPoll.Answer1) Percentage  
FROM DailyPoll

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
attipaAuthor Commented:
worked like a charm!  one last thing....how can i change getting 50.00000000000% to 50%
0
attipaAuthor Commented:
nevermind...i got it.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Just cast it to int to truncate decimal values..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.