Count with null value return zero

Navigation
3

I made an insert into a table and now I need to make a select in that table by making a count in a given column.

The question is that this column (which I gave the request name) has null value for some situations and when I do the count, I would like to indicate 0 (zero) for these situations (currently it is returning 1) and for the columns which has nonzero value, count as usual.

Can someone please help me set up a command for this? is it possible to return the information in 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;
    
asked by anonymous 31.10.2018 / 19:34

2 answers

5

You can use a combination of conditional sum to add zero or one as appropriate.

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
    
31.10.2018 / 19:41
1

If you use postgres 9.4+, use the FILTER command (See the 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;
    
31.10.2018 / 19:44
Effect of increasing and decreasing border size Difference between // and / * ... * /