Mysql return 0 not null in sum

Question

Help needed please I have a table with taskId, Materials, Labour and a table with expenses in. The issue i have is that some tasks do not have and expense column in the taskenpense table so the column returns null. I need null to be 0.

       ` CREATE TABLE emptasks (  empTaskId INT,  taskMaterials NUMERIC(8,2),taskLabour NUMERIC(8,2));
        INSERT INTO emptasks VALUES
      (1,  50, 50),
      (2,   450.26, 50),
      (3,  2505.10, 50),
       (4, 2505.10, 50),
       (5, 500, 500),
       (6, 1000, 50);


    CREATE TABLE taskexpenses (
      feeID INT,
      empTaskId INT,
      expense NUMERIC(8,2));

    INSERT INTO taskexpenses VALUES
    (1, 1,  50.00),
      (1, 2,  50.00),
      (2, 2, 126.00),
      (3, 3,  50.00),
      (4, 4,  50.00),
      (2, 2, 1206.00);

SELECT
    p.empTaskId,
      p.Labour,
      p.Materials,
      f.Expenses,
      p.Labour + p.Materials - f.Expenses AS Total,
   ROUND( (f.Expenses  + p.Materials) / p.Labour * 100, 2) AS Percentage
    FROM (
      SELECT
      empTaskId, 
        SUM(taskMaterials) AS Labour,
      SUM(taskLabour) AS Materials
      FROM emptasks
      GROUP BY empTaskId
    ) p
    LEFT JOIN (
      SELECT taskexpenses.empTaskId,

      SUM(expense) AS Expenses
      FROM emptasks
      INNER JOIN taskexpenses ON emptasks.empTaskId = taskexpenses.empTaskId
      GROUP BY empTaskId
    ) f ON p.empTaskId = f.empTaskId

the result is
empTaskId   Labour  Materials   Expenses    Total   Percentage
1            50        50        50          50      200
2          450.26      50       1382        -881.74 318.04
3          2505.1      50       50          2505.1  3.99
4          2505.1      50       50          2505.1  3.99
5           500         500    (null)      (null)   (null)
6          1000        50      (null)      (null)   (null)

I need the null value to return 0 so the sum can be worked out FIDDLE LINK THanks Jon

Bob Jarvis - Слава Україні · Answer 1 · 2015-08-19T10:59:55.633

Use the COALESCE function:

SELECT p.empTaskId,
       p.Labour,
       p.Materials,
       COALESCE(f.Expenses, 0) AS Expenses,
       COALESCE(p.Labour, 0) + COALESCE(p.Materials, 0) - COALESCE(f.Expenses, 0) AS Total,
      ROUND( (COALESCE(f.Expenses, 0) + COALESCE(p.Materials, 0)) / p.Labour * 100, 2) AS Percentage
  FROM (SELECT empTaskId, 
               SUM(COALESCE(taskMaterials, 0)) AS Labour,
               SUM(COALESCE(taskLabour, 0)) AS Materials
          FROM emptasks
          GROUP BY empTaskId) p
  LEFT JOIN (SELECT taskexpenses.empTaskId,
                    SUM(COALESCE(expense, 0)) AS Expenses
               FROM emptasks
               INNER JOIN taskexpenses
                 ON emptasks.empTaskId = taskexpenses.empTaskId
               GROUP BY empTaskId) f
    ON p.empTaskId = f.empTaskId

Note that here I've put COALESCE on just about everything which might possibly be NULL. If you only want to put it on the Expenses column change it to be what you want.

Best of luck.

score 1 · Accepted Answer · answered Aug 19 '15 at 11:01

1

Slightly simpler than in the answer from @Bob Jarvis is to use the IFNULL() function.

SELECT
    p.empTaskId,
      p.Labour,
      p.Materials,
      IFNULL(f.Expenses, '0') AS Expenses,
      IFNULL(p.Labour + p.Materials - f.Expenses, '0') AS Total,
   IFNULL(ROUND( (f.Expenses  + p.Materials) / p.Labour * 100, 2), '0') AS Percentage
    FROM ...

See fiddle

answered Aug 19 '15 at 11:01

marekful

14,986
6
37
59

In line 6 above, zero will be returned if `Labour` and `Materials` are non-NULL but `Expenses` is NULL (because non-NULL + non-NULL - NULL is NULL). – Bob Jarvis - Слава Україні Aug 19 '15 at 11:04
That's an unlikely scenario @Bob Jarvis. Either Expenses, Total and Percentage are all NULL because the LEFT JOIN didn't find a row to join, or all numbers. – marekful Aug 19 '15 at 11:09
@marekful Hi thanks for your answer but the query does not work check your fiddle example the total on id 5 and 6 should not be zero 5 should be total 1000 and 6 should be total 1500 `IFNULL(p.Labour + p.Materials - f.Expenses, '0') AS Total,` labour + material - expenses = total Any ideas Thank Jon – jonathan young Aug 19 '15 at 13:20
OK, since the `taskexpenses` table may not have a corresponding row for `emptasks` the `IFNULL` function calls needed to be rearranged so they're applied to `f.Expenses` instead of the result of the full calculations. New [Fiddle](http://www.sqlfiddle.com/#!2/32182/15) – marekful Aug 19 '15 at 14:14
@marekful How would i had a third table with flueid, empId, taskId, Miles , cost. Could you fiddle it for me LOL I would like to keep flue expenses in another table but still work it into the query thanks for your help cheers Jon – jonathan young Aug 19 '15 at 17:39
@marekful OK think i have sorted it can you take a look for me I am a newbie [FIDDLE](http://www.sqlfiddle.com/#!2/8d62fe/3) IS this OK thanks again for your help – jonathan young Aug 19 '15 at 17:58
@marekful this is great was looking for this exact solution; in my case I have SUM(...) which might result in a NULL value and I wanted 0 returned in that situation. – JMac Aug 02 '16 at 02:30

Mysql return 0 not null in sum

2 Answers2