MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Alvaro Cobo Date:June 10 2005 5:27am
Subject:Re: (SOLVED) CASE .. WHEN .. returns NULL
View as plain text  
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" <sgarner@stripped>
To: "Alvaro Cobo" <coboalvaro@stripped>
Cc: <mysql@stripped>
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

Thread
CASE .. WHEN .. returns NULLAlvaro Cobo10 Jun
  • Re: CASE .. WHEN .. returns NULLSimon Garner10 Jun
  • Re: (SOLVED) CASE .. WHEN .. returns NULLAlvaro Cobo10 Jun
Re: CASE .. WHEN .. returns NULLAlvaro Cobo10 Jun