Is it possible to make that instead of NULL will be displayed 0?

Question

Code:

SELECT 
  count(*) as count, 
  sum(Status = 'test1') as count_test1, 
  sum(Status = 'test2') as count_test2
FROM Table
WHERE Type = 'test'

in result i see:

count   count_test1   count_test2
  0       NULL           NULL

Tell me please is it possible to make that instead of NULL will be displayed 0?

P.S.: That results was:

count   count_test1   count_test2
  0       0               0

score 4 · Accepted Answer · answered Oct 14 '13 at 11:34

4

you can use COALESCE

COALESCE(sum(Status = 'test1'), 0)

or IFNULL

IFNULL(sum(Status = 'test1'), 0)

answered Oct 14 '13 at 11:34

John Woo

258,903
69
498
492

1

use `COALESCE(sum(Status = 'test1'), 0) as count_test1` ? – Artem Ivanov Oct 14 '13 at 11:35

score 1 · Answer 2 · answered Oct 14 '13 at 11:36

1

SELECT 
  count(*) as count, 
      IFNULL(sum(Status = 'test1'), 0) as count_test1, 
      IFNULL(sum(Status = 'test2'), 0) as count_test2
FROM Table
WHERE Type = 'test'

answered Oct 14 '13 at 11:36

Fred

5,663
4
45
74

I feel IFNULL to be more specific in this case. Additional reading: http://stackoverflow.com/questions/4747877/mysql-ifnull-vs-coalesce-which-is-faster – Vincent Tan Oct 14 '13 at 11:43
@VincentTan I believe it is very slightly quicker but I do have to admit not as portable. – Fred Oct 14 '13 at 11:51

score 0 · Answer 3 · answered Oct 14 '13 at 11:37

You can use the COALESCE function which take an array and return the first element not null

SELECT 
  count(*) as count, 
  COALESCE(sum(Status = 'test1'), 0) as count_test1, 
  COALESCE(sum(Status = 'test2'), 0) as count_test2
FROM Table
WHERE Type = 'test'

Is it possible to make that instead of NULL will be displayed 0?

3 Answers3