June 20th, 2013, 03:45 PM

% 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 03:48 PM.
June 20th, 2013, 04:32 PM

Code:
SELECT paid_amount / total_price *100 AS percentage
, total_price  total_comision_part1 /
( paid_amount / total_price *100 ) AS comisions_part2
FROM ........
June 20th, 2013, 04:47 PM

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
June 20th, 2013, 05:59 PM

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?