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]