2011
12-19

mysql sum error

In Blog | : ,
Today I was fighting with strange error.
  1.  
  2. SELECT
  3. -- ...
  4. (
  5. (SELECT SUM(t.v)
  6. FROM tab1 t
  7. )
  8. +
  9. (SELECT SUM(t.v)
  10. FROM tab2 t
  11. )
  12. ) AS x
  13. -- ...
  14.  
Something like that give me NULL in x in every row even if there was a lot of records in tab1. Here is why: If no rows match then SUM return NULL, not 0. And another bad think is NULL + 1 = NULL. So to correct that I need to do like this:
  1.  
  2. SELECT
  3. -- ...
  4. (
  5. IFNULL((
  6. SELECT SUM(t.v)
  7. FROM tab1 t
  8. ),0)
  9. +
  10. IFNULL((
  11. SELECT SUM(t.v)
  12. FROM tab2 t
  13. ),0)
  14. ) AS x
  15. -- ...
  16.  
Now it work. I hope it will help you.
No Coments jet.

Leave a Reply

  ( Ctrl+Enter )