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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql