'How to count null values in postgresql?

select distinct "column" from table;

output:

    column
1     0.0
2     [null]
3     1.0

But when I try to count the null values

select count("column") from train where "column" is NULL;

Gives output 0 (zero)

Can you suggest where it's going wrong?

sqlpostgresqlcountnull


Solution 1:[1]

Use SUM

SELECT SUM(CASE WHEN column IS NULL THEN 1 ELSE 0 END) AS column_null_tally
FROM table;

Solution 2:[2]

Some workaround when you want to count the values on aggregations, including NULL ones, but can't use count(*) (if other columns are different too).

On these cases, you can use this request :

count(distinct("column")) + (CASE bool_or("column" is null) WHEN true THEN 1 ELSE 0 END)

The count(distinct(column)) will count the non null values, and the other part will add 1 if there is a null value

Solution 3:[3]

You get zero because you are counting null (empty) values, you need to count values from a field that is not empty, like the id field.

select count("id_column") from train where "data_column" is NULL;

Solution 4:[4]

Use FILTER

SELECT
  COUNT(*) FILTER (WHERE "column" IS NULL) AS is_null,
  COUNT(*) FILTER (WHERE "column" < 1.0) AS lt_one,
  COUNT(*) FILTER (WHERE "column" > 1.0) AS gt_one,
  COUNT(*) FILTER (WHERE "column" = 1.0) AS just_perfect
FROM "table";

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Eldar Agalarov
Solution 2 Guigoz
Solution 3 Luis Garcia
Solution 4 Vasilii Suricov

Related Questions