Count with null value return zero

Question

I made an Insert in a table and now I need to make a select in this table making a Count in a given column.

The point is that this column (which I gave the request name) has null value for some situations and when I do Count, I would like it to be 0 (zero) for these situations (currently it is returning 1) and for columns that have non-zero value, do Count normally.

Can someone help me, please, to set up a command for that ? Is it possible to return the information this way ?

select 0,
a.seqpessoa,
a.nomerazao,
a.cidade,
a.uf,
a.bairro,
a.fone,
a.dtaultfat,
a.datref,
a.equipe,
count (a.pedido) as pedido
from PAS_CLI_CAD_EQUIPE_PEDIDO a
group by a.seqpessoa,
a.nomerazao,
a.cidade,
a.uf,
a.bairro,
a.fone,
a.dtaultfat,
a.datref,
a.equipe;

Answer 1

You can use a conditional sum combination to sum zero or one as the case may be.

Example compatible with various bases:

SELECT SUM(CASE WHEN a.pedido IS NULL THEN 0 ELSE 1 END) AS total

Example for Mysql:

SELECT SUM(IF(a.pedido IS NULL, 0, 1)) AS total

Answer 2

If you use 9.4+ postgres, use the FILTER command (See doc here). Works for any aggregation function. See:

select 0,
a.seqpessoa,
a.nomerazao,
a.cidade,
a.uf,
a.bairro,
a.fone,
a.dtaultfat,
a.datref,
a.equipe,
count (a.pedido) FILTER (WHERE a.pedido IS NOT NULL)as pedido
from PAS_CLI_CAD_EQUIPE_PEDIDO a
group by a.seqpessoa,
a.nomerazao,
a.cidade,
a.uf,
a.bairro,
a.fone,
a.dtaultfat,
a.datref,
a.equipe;