Thread: % in a query

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0

    % in a query


    Hi,
    In a query I want to calculate the percentage of total price on a paid amount.
    Then I need to use the result, the percentage I got to calculate the comision of that paid amount.
    So I want to do something like this:

    Code:
    SELECT paid_amount / total_price *100 AS percentage, total_price - total_comision_part1 / percentage AS comisions_part2
    FROM ........
    This works perfect:
    Code:
    paid_amount / total_price *100 AS percentage
    however I get as a result instead of for exampel 50 (%) I get 50.000000

    This does not work at all:
    Code:
    total_price - total_comision_part1 / percentage AS comisions_part2
    and not even sure its the correct way, as I need to divide with the result in percentage,
    and of course I get that porcentage is an unknown column .
    Any idea?
    Last edited by newtonperri; June 20th, 2013 at 02:48 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Code:
    SELECT paid_amount / total_price *100 AS percentage
         , total_price - total_comision_part1 / 
           ( paid_amount / total_price *100 ) AS comisions_part2
    FROM ........
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT paid_amount / total_price *100 AS percentage
         , total_price - total_comision_part1 / 
           ( paid_amount / total_price *100 ) AS comisions_part2
    FROM ........
    Thanks, does not give any error, but does not give the correct amount.
    Changing the query to use just number is easier and gives same result:
    Code:
    SELECT 300 / 600 *100 AS porcentajes, 600 - 400 / ( 300 / 600 *100 ) AS comisiones
    FROM pagados_fracciones
    gives me as result:
    50.0000 592.0000

    The 50% is correct but the comisions_part2 in this case should be 100, as the amount 300 been paid
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    wow,
    I got it

    SELECT 300 / 600 *100 AS porcentajes, ( 300 / 600 *100 ) * (600 - 400)/100 AS comisiones
    FROM pagados_fracciones
    LEFT JOIN pagados ON pagados.id_reserva = pagados_fracciones.id_reserva

    Nearly perfect, except that I get:
    porcentage comisions
    50.0000 100.00000000

    apart from using php any way to get rid of the ceros?

IMN logo majestic logo threadwatch logo seochat tools logo