Percent and SQL Problem

1. Senior Member
Join Date
Dec 1969
Posts
508

Percent and SQL Problem

Hi, I am trying to calculate percentages on a column in SQL Server 2000. I have gotten the equation correct, but I cannont figure out how to get rid of some of the numbers behind the decimal. Here is my query:<BR><BR>SELECT DISTINCT<BR> PctA = (((SELECT CONVERT(NUMERIC(5,2),COUNT(PollAns)) FROM Poll WHERE PollAns = 1) / (SELECT CONVERT(NUMERIC(5,2),COUNT(PollAns)) FROM Poll) ) * 100)<BR>FROM<BR> POLL<BR><BR>returns numbers like 4.87804800<BR><BR>I would like for only 2 digits to show behind the decimal (4.87). No matter what I do, they will not go away. I thought when I did "NUMERIC(5,2)" that meant "###.##"....dunno, please help.<BR><BR>- Mike G.<BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

You are converting too soon...

...but it still might not work, because when you convert the number from SQL Server to VBScript it loses all formatting.<BR><BR>But they way you are doing the calculations seems wrong, anyway. Just for starters, why the DISTINCT? When you are only going to get ONE result, no matter what.<BR><BR>SELECT CONVERT( NUMERIC(5,2), <BR> SUM(CASE WHEN PollAns=1 THEN 1 ELSE 0 END) /<BR> COUNT(PollAns)<BR> ) <BR>FROM Poll <BR>

3. Senior Member
Join Date
Dec 1969
Posts
508

RE: You are converting too soon...

Yeah, when I ran the Query, it would choose N number of rows that were in the database. For example, there are 35 rows now. When I ran that query, it would return 4.87000 35 times. Let me try your query out.

4. Senior Member
Join Date
Dec 1969
Posts
508

RE: Thanx Forum

I found some nice little graph code on this site, I just changed it a little and I am using it. I appreciate the help, Mike G.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•