I created a new column called ‘Percentage’ that calculates the percentage increase between Expected and Actual payment. The calculation seems right but when I put it in descending order, 9% is right below 90% when 9% should be at the bottom of the list.
In addition to this, i’d like to remove the additional .00 at the end of each number.
Current code:
SELECT [ExpectedPayment]
,[TotalActualPayment]
,CONCAT(ROUND(((TotalActualPayment-ExpectedPayment)/(ExpectedPayment)*100), 0), '%') 'Percentage'
FROM DATABASE
ORDER BY PERCENTAGE DESC;
Current output:
| ExpectedPayment | TotalActualPayment | Percentage |
|---|---|---|
| 23924.8962 | 46096.44 | 93.00% |
| 24083.0961 | 46355.47 | 92.00% |
| 18937.6445 | 36100.58 | 91.00% |
| 18377.3185 | 20123.11 | 9.00% |
| 17537.915 | 19174.82 | 9.00% |
| 24083.0961 | 45515.82 | 89.00% |
| 26064.4709 | 49300.18 | 89.00% |
Expected output:
| ExpectedPayment | TotalActualPayment | Percentage |
|---|---|---|
| 23924.8962 | 46096.44 | 93% |
| 24083.0961 | 46355.47 | 92% |
| 18937.6445 | 36100.58 | 91% |
| 24083.0961 | 45515.82 | 89% |
| 26064.4709 | 49300.18 | 89% |
| 18377.3185 | 20123.11 | 9% |
| 17537.915 | 19174.82 | 9% |
Advertisement
Answer
I suggest ordering per Gordon’s suggestion. You can remove the .000 at the end by casting it as type int
SELECT [ExpectedPayment] ,[TotalActualPayment] ,CONCAT(cast(ROUND(((TotalActualPayment-ExpectedPayment)/(ExpectedPayment)*100), 0) as int), '%') 'Percentage' FROM DATABASE order by (TotalActualPayment-ExpectedPayment)/(ExpectedPayment) desc
If you are ok with truncating the result of the expression, you can remove the round statement altogether.