# SQL query to return percentage with two decimals

The sript below returns the percentage of a certain product from my Products table.
I would like to display the percentage with two decimals. How can this script be modified in order to achieve that.
``````<%
sqlCount.Prepared = true
sqlCount.CommandText = "SELECT ( count( id ) * 100  / ( SELECT count( * ) FROM Products ) ) as avg FROM Products WHERE myProduct='Beer'"
set objRs = sqlCount.Execute
CountID = objRs("avg")
%>
<%=CountID%>
<%
ObjRs.Close
Set ObjRs = Nothing
%>
``````
###### Who is Participating?
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.

Commented:
How does this work out?

SELECT CONVERT(VARCHAR, CAST(( count( id ) * 100  / ( SELECT count( * ) FROM Products ) AS DECIMAL (20,2))) as avg
0
IT DeveloperAuthor Commented:
It gives me an error message:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'AS'.
0
Microsoft SQL Server Developer, Architect, and AuthorCommented:
If this solution worked in your previous question, you can use numeric(19,2), or maybe decimal(19,2).
0
IT DeveloperAuthor Commented:
jimhorn: I tried both numeric and decimal but it returns something like this:
0.11054637865311308767

It should actually return approximately 11.05
0
Commented:
One more close bracket sorry

SELECT CONVERT(VARCHAR, CAST(( count( id ) * 100  / ( SELECT count( * ) FROM Products ) AS DECIMAL (20,2)))) as avg
0

Experts Exchange Solution brought to you by

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

IT DeveloperAuthor Commented:
I was playing with it and this finally made it work:

SELECT cast(cast(COUNT(id) as decimal)*100 / cast((SELECT COUNT(*) FROM Products)as decimal )as decimal(5,2)) as avg FROM Products WHERE myProduct='Beer'

0
freelancerCommented:
:) money is also a convenient 2 decimals way,
and multiplication by 100.0 (or even multiply by 1.0) can help as well:
(the varchar casting is so you can see it)
``````    SELECT
cast( (count(id) * 100 / 5)     as varchar) an_int
, cast( (count(id) * 100.0 / 5)   as varchar) a12zero
, cast( (count(id) * 100 / 5.0)   as varchar) a6zero
, cast( (count(id) * 100.0 / 5.0) as varchar) a6zero2

, cast( cast( (count(id) * 100 / 5)     as money) as varchar) money1
, cast( cast( (count(id) * 100.0 / 5)   as money) as varchar) money2
, cast( cast( (count(id) * 100 / 5.0)   as money) as varchar) money3
, cast( cast( (count(id) * 100.0 / 5.0) as money) as varchar) money4

, cast( cast( (count(id) * 100 / 5)     as decimal(18,2)) as varchar) dec1
, cast( cast( (count(id) * 100.0 / 5)   as decimal(18,2)) as varchar) dec2
, cast( cast( (count(id) * 100 / 5.0)   as decimal(18,2)) as varchar) dec3
, cast( cast( (count(id) * 100.0 / 5.0) as decimal(18,2)) as varchar) dec4

FROM supportContacts

**[Results][2]**:

| AN_INT |         A12ZERO |    A6ZERO |   A6ZERO2 | MONEY1 | MONEY2 | MONEY3 | MONEY4 |  DEC1 |  DEC2 |  DEC3 |  DEC4 |
|--------|-----------------|-----------|-----------|--------|--------|--------|--------|-------|-------|-------|-------|
|     40 | 40.000000000000 | 40.000000 | 40.000000 |  40.00 |  40.00 |  40.00 |  40.00 | 40.00 | 40.00 | 40.00 | 40.00 |

-- your mileage may vary, but may be seen at:

[1]: http://sqlfiddle.com/#!3/1fa93/9425
``````
0
Microsoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim

<Potentially stupid question>

Do you have the ability to control the decimal numbers on the front end control?
Reason I ask is I've been burned before when the detail section is rounded to two numbers, and some anal-retentive type will manually sum them on their own, then tell me that my totals percentage is a couple of pennies off.

In these cases I just set the decimal places to four, rerun the report, and do a 'here you go, knock yourself out.'
0
IT DeveloperAuthor Commented:
PortletPaul - Thanks for the tip. I was trying to use it with my query, but I must be doing something wrong, it gives me an error

jimhorn - I can't control the decimal, when I sum it up manually it won't display the correct number. I hope my users won't be so anal as yours.

It seems that my solution is not correct; I've just posted another question:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28245175.html
0
Microsoft SQL Server Developer, Architect, and AuthorCommented:
>It seems that my solution is not correct; I've just posted another question:
If that's the case, and the original question has not changed, then it would be better to reopen this question and continue to work the problem.

Not that experts mind the points, but it doesn't do EE any good to have accepted questions out there that are not a correct answer.
0
IT DeveloperAuthor Commented:
It seemed to be correct, when I have more records. It displayed the right numbers when I closed this question. Now I emptied the database and entered again 3 records. When I check now the percentage, instead of 33.33% the percentage displayed is 3.03%
Isn't this strange?
0
Microsoft SQL Server Developer, Architect, and AuthorCommented:
Yep strange.  Unfortunately experts here don't have access to your source of data, so our ability to provide a solution is going to be limited.

About the best we can do here is for you to provide some sample data in the question, and then at least we can verify that any T-SQL we cough up works correct for that data.

Good luck.
0
IT DeveloperAuthor Commented:
My mistake. The query is correct, I just forgot to put the condition in it when I used it in a different environment. I am so sorry for wasting your time.
So, this is the right way to do it:

SELECT cast(cast(COUNT(id) as decimal)*100 / cast((SELECT COUNT(*) FROM Products)as decimal )as decimal(5,2)) as avg FROM Products WHERE myProduct='Beer'
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

From novice to tech pro — start learning today.