CODEWITHSUNDEEP
sqloracle-database
Soban Ali
Soban Ali

Reputation: 25

Calculating the percentage of Difference between two columns as a separate column in the result

I am stuck in a situation that needs percentage between two columns difference, for example: I have column "AVERAGE_PRICE_2017" values and column "AVERAGE_PRICE_2016" values in numbers, Now I need that how much percentage of difference is between these two value. Now the challenge is that the difference result could be in 0 or a negative number. I tried below but didn't work out.

SELECT medicine_name,
  average_price_2017,
  average_price_2016,
  average_price_2017-average_price_2016 a,
  ROUND(a * 100.0 / AVERAGE_PRICE_2017, 1) AS Percent
FROM ST_COMPARE_ALL_4;

ERROR:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'A'
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:
Error at Line: 14 Column: 7

Upvotes: 1

Views: 7845

Answers (1)

codeLover
codeLover

Reputation: 2592

You cannot use alias of any column directly to populate another column. Instead you should find the difference again while calculating percentage. Try the below query: 

SELECT medicine_name,
  average_price_2017,
  average_price_2016,
  average_price_2017-average_price_2016 a,
  ROUND((average_price_2017-average_price_2016) * 100.0 / AVERAGE_PRICE_2017, 1) AS Percent
FROM ST_COMPARE_ALL_4;

Upvotes: 4

Related Questions