CODEWITHSUNDEEP
databasepostgresql
Jorge Roberto
Jorge Roberto

Reputation: 157

Counting columns null and not null in PostgreSql

I need count columns that has null values and not null values for each row, but i don't have idea of how i can do this in PostgreSql.

Upvotes: 1

Views: 5826

Answers (2)

Björn Nilsson
Björn Nilsson

Reputation: 3773

You just need to specify the column name in the count(), it will skip the null:s

select avg(c),count(c) from (select generate_series(1,10) union select null) as a(c);

Ignore the stuff after from, it's just to return a list of values

To make it more clear:

select
  avg(c),
  count(c) count_column,
  count(*) count_star,
  sum(c),
  array_agg(c)
from (
  select generate_series(1,10) union select null order by 1
) as a(c);

        avg         | count_column | count_star | sum |          array_agg          
--------------------+--------------+------------+-----+-----------------------------
 5.5000000000000000 |           10 |         11 |  55 | {1,2,3,4,5,6,7,8,9,10,NULL}
(1 row)

Upvotes: 4

Petr Hejda
Petr Hejda

Reputation: 43491

SELECT COUNT(*) as countNullRows FROM yourTable WHERE columnName IS NULL;
SELECT COUNT(*) as countNonNullRows FROM yourTable WHERE columnName IS NOT NULL;

Upvotes: 1

Related Questions