query problem

[koko007]

Hey All,

.. say I had the following query:

select fld_a, sum(fld_b), avg(fld_b) from tbl_ab group by fld_a

and also say that fld_a can take various values, however in the result from the query above I want to combine all "NULL" and "0" values under one category.

Example:
instead of getting as a resulet:
NULL 200 20.0
Hell 120 18.776
0 10 10

I want to get NULL and 0 combined in the same category like this:
NULL 210 18.0
Hell 120 18.776

Any ideas?

 

[TonyGroves]

You could use:
[tt]
GROUP BY IFNULL(fld_a,0)
[/tt]

 

[r937]

use COALESCE instead of IFNULL

same result, but it means you won't have to unlearn/relearn the technique should you ever write sql for another database, since COALESCE is standard sql (as far as i know)



r937.com | rudy.ca