How to count null values in postgresql?

Question

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?

Hint : use count(*) instead of column_name.

Yogesh Sharma
– Yogesh Sharma

2018-12-26 16:02:59 +00:00
Commented Dec 26, 2018 at 16:02 — Yogesh Sharma
– Yogesh Sharma, Commented Dec 26, 2018 at 16:02

Eldar · Accepted Answer · 2021-02-10 11:29:45Z

25

Use SUM

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

answered Feb 10, 2021 at 11:29

Eldar

5,2874 gold badges33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vasilii Suricov Over a year ago

SUM((column IS NULL)::int) AS column_null_tally will do the same

Gordon Linoff · Accepted Answer · 2018-12-26 16:03:08Z

24

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.

answered Dec 26, 2018 at 16:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Guigoz · Accepted Answer · 2020-01-27 14:40:02Z

8

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

answered Jan 27, 2020 at 14:40

Guigoz

7592 gold badges9 silver badges15 bronze badges

Comments

Vasilii Suricov · Accepted Answer · 2022-02-04 14:25:39Z

8

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

answered Feb 4, 2022 at 14:25

Vasilii Suricov

97514 silver badges19 bronze badges

Comments

Luis Garcia · Accepted Answer · 2021-12-23 23:48:26Z

0

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;

answered Dec 23, 2021 at 23:48

Luis Garcia

1

Comments

questionto42 · Accepted Answer · 2023-11-16 11:30:56Z

0

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).

edited Nov 16, 2023 at 11:30

answered Dec 17, 2022 at 23:31

questionto42

9,9228 gold badges80 silver badges125 bronze badges

Collectives™ on Stack Overflow

How to count null values in postgresql?

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related