[mysql] MySql: is it possible to 'SUM IF' or to 'COUNT IF'?

I have a column 'hour' I have a column 'kind' (it can be 1,2 or 3)

I'd like to do something like:

SELECT count(id), SUM(hour) as totHour, SUM( IF ( kind = 1, 1, 0 ) ) as countKindOne

or

SELECT count(id), SUM(hour) as totHour, COUNT( IF ( kind = 1 ) ) as countKindOne

But mysql tell me I've an error... what's the error!?

Please see this stackoverflow topic: MySQL SUM IF field b = field a

.. I'm not able to reply this ...

You can also use SUM + IF which is shorter than SUM + CASE:

SELECT
    count(id)
    , SUM(IF(kind=1, 1, 0)) AS countKindOne
    , SUM(CASE WHEN kind=2 THEN 1 ELSE 0 END) AS countKindTwo

From MYSQL I solved the problem like this:

SUM(CASE WHEN used = 1 THEN 1 ELSE 0 END) as amount_one,

Hope this helps :D

There is a slight difference between the top answers, namely SUM(case when kind = 1 then 1 else 0 end) and SUM(kind=1).

When all values in column kind happen to be NULL, the result of SUM(case when kind = 1 then 1 else 0 end) is 0, whereas the result of SUM(kind=1) is NULL.

An example (http://sqlfiddle.com/#!9/b23807/2):

Schema:

CREATE TABLE Table1
(`first_col` int, `second_col` int)
;

INSERT INTO Table1
    (`first_col`, `second_col`)
VALUES
       (1, NULL),
       (1, NULL),
       (NULL, NULL)
;

Query results:

SELECT SUM(first_col=1) FROM Table1;
-- Result: 2
SELECT SUM(first_col=2) FROM Table1;
-- Result: 0
SELECT SUM(second_col=1) FROM Table1;
-- Result: NULL
SELECT SUM(CASE WHEN second_col=1 THEN 1 ELSE 0 END) FROM Table1;
-- Result: 0

you want something like:

SELECT count(id), SUM(hour) as totHour, SUM(kind=1) as countKindOne;

Note that your second example was close, but the IF() function always takes three arguments, so it would have had to be COUNT(IF(kind=1,1,NULL)). I prefer the SUM() syntax shown above because it's concise.

It is worth noting that you can build upon Gavin Toweys answer by using multiple fields from across your query such as

SUM(table.field = 1 AND table2.field = 2)

You can also use this syntax for COUNT and I am sure other functions as well.