MYSQL - Dynamic Column Type Change

MySQL & MariaDB Other MySQL® Questions
1

Hi,
I am trying to run one query in my Mysql Database to calculate Gross Profit nd Margins from few tables.

I’m selecting final value of gross profit in query like :

format(((prod.selling_price + ship.rate) - (prod.cost_price + ship.shipping_cost + sup.fee_per_order + sup.extra_fee_per_product + sup.other)),2) as gross_profit.

Now I want to sort results on this gross_profit ASC / DESC depends on requirement - when im doing sorting on this gross_profit its not giving accurate result coz its taking values of gross_profit as string.

Is ny way i can convert this gross_profit to INT so my sorting give accurate results or any other way to get accurate result in sorting?

I tried to use like ORDER BY CAST(gross_profit AS SIGNED) but not work as i understand its not column from table it self.

Can any one help me to sort out this?..

Thanks in Advance !

2

Try

CAST(gross_profit AS decimal(65,4))

3

Hi,

Thanks for reply )

This is what im gettig now…

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘decimal ( 65 , 4 ) ) DESC
LIMIT 0, 30’ at line 1

so i think ORDER BYCAST(
gross_profit AS decimal( 65, 4 )
) will not work as well…

Thanks !!!