CODEWITHSUNDEEP
sqlpostgresqlcountnull
Surjya Narayana Padhi
Surjya Narayana Padhi

Reputation: 7841

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?

Upvotes: 19

Views: 32019

Answers (6)

questionto42
questionto42

Reputation: 9542

Since

  • select count(coalesce(t."column", 0)) from table t is the full number of rows, with and without NULL:s and
  • select count(t."column") from table t is the number of rows without NULL:s,

this works as well:

Select count(coalesce(t."column", 0)) - count(t."column") FROM table t;

(This answer might also help those who came here to count both NULL and not NULL, at least I was stranded here since I was searching for that).

Upvotes: 0

Vasilii Suricov
Vasilii Suricov

Reputation: 954

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";

Upvotes: 8

Luis Garcia
Luis Garcia

Reputation: 1

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;

Upvotes: 0

Eldar
Eldar

Reputation: 5227

Use SUM

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

Upvotes: 24

Guigoz
Guigoz

Reputation: 739

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

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Use count(*):

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

count() with any other argument counts the non-NULL values, so there are none if "column" is NULL.

Upvotes: 24

Related Questions