June 20th, 2013, 02:45 PM
% in a query
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:
This works perfect:
SELECT paid_amount / total_price *100 AS percentage, total_price - total_comision_part1 / percentage AS comisions_part2
however I get as a result instead of for exampel 50 (%) I get 50.000000
paid_amount / total_price *100 AS percentage
This does not work at all:
and not even sure its the correct way, as I need to divide with the result in percentage,
total_price - total_comision_part1 / percentage AS comisions_part2
and of course I get that porcentage is an unknown column .
Last edited by newtonperri; June 20th, 2013 at 02:48 PM.
June 20th, 2013, 03:32 PM
SELECT paid_amount / total_price *100 AS percentage
, total_price - total_comision_part1 /
( paid_amount / total_price *100 ) AS comisions_part2
June 20th, 2013, 03:47 PM
Thanks, does not give any error, but does not give the correct amount.
Originally Posted by r937
Changing the query to use just number is easier and gives same result:
gives me as result:
SELECT 300 / 600 *100 AS porcentajes, 600 - 400 / ( 300 / 600 *100 ) AS comisiones
The 50% is correct but the comisions_part2 in this case should be 100, as the amount 300 been paid
June 20th, 2013, 04:59 PM
I got it
SELECT 300 / 600 *100 AS porcentajes, ( 300 / 600 *100 ) * (600 - 400)/100 AS comisiones
LEFT JOIN pagados ON pagados.id_reserva = pagados_fracciones.id_reserva
Nearly perfect, except that I get:
apart from using php any way to get rid of the ceros?