GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MYSQL - Dynamic Column Type Change

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MYSQL - Dynamic Column Type Change

    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))

    Comment


    • #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 !!!

      Comment


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


        is there a space between "BYCAST" By and Cast when you executed?

        Comment


        • #5
          Hi..

          Yes there is space.

          I typed worng here.I run actually ORDER BY CAST(
          gross_profit AS decimal( 65, 4 )
          )
          this only and getting this error.

          Thanks !!!

          Comment

          Working...
          X