Thanks Simon: That was marvelous!!!. It have done the job!!!. I was hours trying to solve it and it was such a simple thing :-).
I am very grateful with you and with the list. Best regards, Alvaro. PD: You are the best guys! ----- Original Message ----- From: "Simon Garner" <[EMAIL PROTECTED]> To: "Alvaro Cobo" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Friday, June 10, 2005 12:00 AM Subject: Re: CASE .. WHEN .. returns NULL > 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]
