Alvaro Cobo wrote:
Hi guys:
I am here again asking for your generous and wise advise:
I have a table (tbl_f4Granjas) with the information of some farms, their whole land extension (field: GraExtUPA) and the portion of the land they are managing in an agro-ecological way (field: GraExtPredio).
What do I need is to get the percentage by organization (field: FK_ProjectHolderId) of the land managed in the agroecological way. After that, I need to recode the data based in a range (0%-10%=0; 11%-25%=3, etc.).
I am using the CASE .. WHEN .. THEN .. function, but it keeps giving me a NULL result. What I am doing wrong?. I have searched everywhere, goggled it, check the Mysql manual, but I couldnī't find the answer. My server is: Mysql 4.1.10 in a Debian Sarge box.
I think your problem is you are saying "<=10" and then ">=11" for the
next level, but your percentage could be e.g. 10.55, which wouldn't
match any of your CASEs.
This should work...
SELECT
a.FK_ProjectHolderId,
CASE
WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) >75 THEN 12
WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) >50 THEN 9
WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) >25 THEN 6
WHEN (SUM(a.GraExtPredio)/SUM(a.GraExtUPA)*100) >10 THEN 3
ELSE 0 END
AS agroland
FROM
tbl_f4Granjas AS a
GROUP BY
FK_ProjectHolderId
-Simon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
