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: https://stackoverflow.com/questions/9083283/mysql-sum-if-field-b-field-a

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

You can use a CASE statement:

SELECT count(id), 
	SUM(hour) as totHour, 
	SUM(case when kind = 1 then 1 else 0 end) as countKindOne

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.

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

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

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

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.