Query to sum some values of a column with a condition

Question

Having a table with this values:

name     | executing | failed |
-------------------------------
task1         0          1
task2         1          0
task3         1          0
task4         0          0

With a query i want to get:

• The total amount of executing task (2 in the example, task2 and task3)
• The total amount of failed task (1 in the example, task1)
• The total amount of pending task (those that are executing=0 and failed=0, 1 in the example, task4)

I can get the first two by uysing the following query:

SELECT IFNULL(SUM(executing), 0) Executing, IFNULL(SUM(failed), 0) Failed FROM mytable;

How can I expand my query so I can get another column with the sum of pending tasks?

Thanks in advance

Expected output:

executing | failed | pending
----------------------------
     2         1          1  

Answer 1

SELECT SUM(executing) AS Executing
    , SUM(failed) as Failed
    , SUM(CASE 
        WHEN executing = 0 AND failed = 0 THEN 1
        ELSE 0 END
    ) AS Pending
FROM mytable;

Answer 2

You don't specify how you want the results. I would do this as:

select (case when executing = 1 and failed = 0 then 'Executing'
             when failed = 1 then 'Failed'
             when executing = 0 and failed = 0 then 'Pending'
             else 'Unknown'
        end) as status, count(*) as cnt
from t
group by status;

You can also easily pivot the data using conditional aggregation:

select sum(executing = 1 and failed = 0) as Executing,
       sum(failed = 1) as Failed,
       sum(executing = 0 and failed = 0) as Pending
from t;

This uses a MySQL shorthand that treats boolean expressions as numbers -- with "1" for true and "0" for false.