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.
2
-
Make your question clear.tchelidze– tchelidze2016-01-24 16:21:25 +00:00Commented Jan 24, 2016 at 16:21
-
@tchelidze you can better understand ?Jorge Roberto– Jorge Roberto2016-01-24 16:31:10 +00:00Commented Jan 24, 2016 at 16:31
Add a comment
|
2 Answers
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)
8 Comments
Jorge Roberto
how can add my select from table in this query ? i tried change from select null to select columnsName from table, and not working.
Björn Nilsson
select count(column_name), avg(column_name) from table_name
Jorge Roberto
but this " select count(column_name), avg(column_name) from table_name" is in with union in select generate_series not ? i don't understand where my select table enters it.
Björn Nilsson
You don't need the generate series stuff at all, select count(column_name), avg(column_name) from table_name is the complete query
Björn Nilsson
Sorry, don't understand what you mean.Please add your table to the question and the query you are trying with. Also an example of what output you want
|
SELECT COUNT(*) as countNullRows FROM yourTable WHERE columnName IS NULL;
SELECT COUNT(*) as countNonNullRows FROM yourTable WHERE columnName IS NOT NULL;
5 Comments
Jorge Roberto
rowName is name of each column ??
Petr Hejda
Sorry, my mistake. I meant to write columnName (editing the answer)... It's name of the column you're checking whether it has null or non-null value.
Jorge Roberto
but is more than one columns, i'll have to add one each one column in my where clause ?
Björn Nilsson
You don't need the WHERE, just count(columnName), it will skip the NULL:s
Jorge Roberto
but he is counting all lines and not for each line. i tried this " SELECT COUNT(*) as countNullRows, * FROM posts group by id "