• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

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.
0
attipa
Asked:
attipa
  • 8
  • 4
  • 2
1 Solution
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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 & ArchitectCommented:
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 & ArchitectCommented:
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 & ArchitectCommented:
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
 
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 & ArchitectCommented:
Just cast it to int to truncate decimal values..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now