Solved

SQL calculate a percentage

Posted on 2013-01-22
254 Views
In this query I'm trying to do a percent calcuation but for some reason anything less than 100% is not being calculated.

``````SELECT #tmp_ALLORDERS_COUNTS.TOTAL_ORDERS_COUNT, CAST(((#tmp_IVPROTOCOL_COUNTS.IVPROTOCOL_ORDERS_COUNT/#tmp_ALLORDERS_COUNTS.TOTAL_ORDERS_COUNT)*100) as decimal(18,2)) AS PERCENTAGE_USE
, #tmp_IVPROTOCOL_COUNTS.* FROM  #tmp_ALLORDERS_COUNTS, #tmp_IVPROTOCOL_COUNTS
WHERE  #tmp_ALLORDERS_COUNTS.DRUG_CODE = #tmp_IVPROTOCOL_COUNTS.DRUG_CODE
``````

results look like this:
0
Question by:ghettocounselor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 35

Accepted Solution

johnsone earned 500 total points
ID: 38807482
It is most likely an issue of data types.  I would cast each individual variable:

CAST(CAST(#tmp_IVPROTOCOL_COUNTS.IVPROTOCOL_ORDERS_COUNT as decimal(18,2))/CAST(#tmp_ALLORDERS_COUNTS.TOTAL_ORDERS_COUNT as decimal(18,2))*100.0) as decimal(18,2))

It is doing integer math and then doing the cast.  You need to do the cast first.
0

Author Comment

ID: 38807963
Needed to wrap with a few more parens but idea was sound. THANKS!

(CAST(CAST(#tmp_IVPROTOCOL_COUNTS.IVPROTOCOL_ORDERS_COUNT as decimal(18,2))/(CAST(#tmp_ALLORDERS_COUNTS.TOTAL_ORDERS_COUNT as decimal (18,2))) as decimal (18,2))*100) AS PERCENTAGE_USE
0

Author Closing Comment

ID: 38807966
As always appreciate quick response!
0

LVL 35

Expert Comment

ID: 38808056
Sorry about the missing parens.  I didn't try it.  It looked like they were all there.  But, you got the idea.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Updating a field based from a if exist.... 2 45
sql query help 15 67
Setting variables in a stored procedure 5 79
MSSQL Query for Selecting the SUM of a Specific Group 2 36
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses
Course of the Month6 days, left to enroll