Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

SQL calculate a percentage

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

Open in new window


results look like this:
percentagecalcsql
0
ghettocounselor
Asked:
ghettocounselor
  • 2
  • 2
1 Solution
 
johnsoneSenior Oracle DBACommented:
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
 
ghettocounselorAuthor Commented:
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
 
ghettocounselorAuthor Commented:
As always appreciate quick response!
0
 
johnsoneSenior Oracle DBACommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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