Re: change a empty value for 0
Hi. Let me explain something about coalesce. coalesce(field,0) return 0 if the field value equals null or the field value if it´s not null. obed [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hi. thanks ! but nop... SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; Empty set (0.00 sec) and with coalesce nop :-( it's the same problem mysql select id_ingenio, (select case when sum( coalesce(cantidad,NULL) ) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.00 sec) the problem is that my sub select returns a empty result, it isn't a null value, but somthing straing is happening if i make only the sub select look mysql select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1 group by id_ingenio; Empty set (0.00 sec) i think that the group by is doing this... becouse look select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1; +--+ | a| +--+ |0 | +--+ 1 row in set (0.01 sec) what can i do ? thanks -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
On 7/26/06, João Cândido de Souza Neto [EMAIL PROTECTED] wrote: Hi. Let me explain something about coalesce. coalesce(field,0) return 0 if the field value equals null or the field value if it´s not null. :-) ooo !!! ok... it's the same to do: case when field is null then 0 else field end Than's a lot everyone !!! i didn't know it -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.01 sec) You can use DEFAULT 0 to set a column to 0 by default. You can also do it in one pass through: (test with this first): SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; if that gives you the correct result set then: UPDATE detalle_transaccion SET cantidad = 0 WHERE cantidad IS NULL; -- Chris White PHP Programmer/DBamBam Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
Try this: select id_ingenio, (select sum(coalesce(cantidad,0)) from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; obed [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hi all ! i have two tables ingenio and detalle_tanque, detalle_tanque has a foreign key to ingenio, i want to show all the ingenio values and a sum of the cantidad field in the detalle_tanque table for each value in ingenio, but one of the ingenio's values it doesn't exist in detalle_tanque, i get this mysql select id_ingenio, (select case when sum(cantidad) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.01 sec) i want to change de value of null to 0, but i don't know why i can't do it with sum(cantidad) is null then 0 else sum(cantidad) end thanks in advanced ... any help it will be good for me ... !!! -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a empty value for 0
Hi. thanks ! but nop... SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; Empty set (0.00 sec) and with coalesce nop :-( it's the same problem mysql select id_ingenio, (select case when sum( coalesce(cantidad,NULL) ) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.00 sec) the problem is that my sub select returns a empty result, it isn't a null value, but somthing straing is happening if i make only the sub select look mysql select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1 group by id_ingenio; Empty set (0.00 sec) i think that the group by is doing this... becouse look select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1; +--+ | a| +--+ |0 | +--+ 1 row in set (0.01 sec) what can i do ? thanks -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: change a empty value for 0
Isn't this over-complicated select id_ingenio, sum(cantidad) from ingenio, detalle_transaccion where ingenio.id_ingenio = detalle_transaccion.id_ingenio group by ingenio.id_ingenio or select id_ingenio, sum(cantidad) from ingenio left join detalle_transaccion on ingenio.id_ingenio = detalle_transaccion.id_ingenio group by ingenio.id_ingenio or select id_ingenio, sum(ifnull(cantidad,0)) from ingenio left join detalle_transaccion on ingenio.id_ingenio = detalle_transaccion.id_ingenio group by ingenio.id_ingenio or am I missing something? Quentin -Original Message- From: obed [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 July 2006 11:12 a.m. To: João Cândido de Souza Neto Cc: mysql@lists.mysql.com Subject: Re: change a empty value for 0 Hi. thanks ! but nop... SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS NULL; Empty set (0.00 sec) and with coalesce nop :-( it's the same problem mysql select id_ingenio, (select case when sum( coalesce(cantidad,NULL) ) is null then 0 else sum(cantidad) end from detalle_transaccion where id_ingenio=ingenio.id_ingenio group by id_ingenio) as cantidad from ingenio LIMIT 5; ++--+ | id_ingenio | cantidad | ++--+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | 2622.77 | | 5 | NULL | ++--+ 5 rows in set (0.00 sec) the problem is that my sub select returns a empty result, it isn't a null value, but somthing straing is happening if i make only the sub select look mysql select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1 group by id_ingenio; Empty set (0.00 sec) i think that the group by is doing this... becouse look select case when sum(cantidad) is null then 0 else sum(cantidad) end as a from detalle_transaccion where id_ingenio=1; +--+ | a| +--+ |0 | +--+ 1 row in set (0.01 sec) what can i do ? thanks -- http://www.obed.org.mx --- blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
