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

Question

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 ...

Answer 1

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 

Answer 2

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.