CODEWITHSUNDEEP
sqloracle-databasedecimalpercentage
Nomad
Nomad

Reputation: 268

How to have a percentage in Oracle SQL show decimals?

I have a query that shows how close a inventory count is to being complete. I would like it to show 2 numbers after the decimal, unless that number is 100 or 0. This is what I'm currently using and it outputs numbers like : 95, 100. I would like them to show 95.14, and 100(without decimal)

NVL(ROUND(count(icqa_process_locations.icqa_count_attempt_id)/count(icqa_processes.icqa_process_id)*100,0),0)||'%' as "Percentage Complete"

Upvotes: 1

Views: 27128

Answers (2)

Nomad
Nomad

Reputation: 268

I have answered my own question. Sorry I'm a newbie! But here is my fix in case anyone has something similar.

I added to_char to the front, and changed *100,0 to *100,2.

to_char(NVL(ROUND(count(icqa_process_locations.icqa_count_attempt_id)/count
    (icqa_processes.icqa_process_id)*100,2),0))||'%' as "Percentage Complete"

Upvotes: 1

bigdavy
bigdavy

Reputation: 108

You actually didn't need add TO_CHAR, you just needed to change *100,0 to *100,2 to tell it how many decimal places you wanted:

NVL(ROUND(count(icqa_process_locations.icqa_count_attempt_id)/count
      (icqa_processes.icqa_process_id)*100,2),0)||'%' as "Percentage Complete"

Upvotes: 5

Related Questions