This is the output of a select * from table1, I have a doubt with count function... I want to count that NULL, in order to do that the proper option is to do this:
select count(*) from table1 where fecha_devolucion is null --> This gives me the proper answer counting 1 however if i do:
select count(fecha_devolucion)
from table1
where fecha_devolucion is null --> this returns 0, why? Isn't the same syntax?
What's the difference between choosing a specific field and * from a table?
From the documentation (http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions032.htm):
If you specify expr, then COUNT returns the number of rows where expr is not null. ...
If you specify the asterisk (*), then this function returns all rows...
In other words, COUNT(fecha_devolucion) counts non-NULL values of that column. COUNT(*) counts the total number of rows, regardless of the values.
This is the another way how you can get the count :
SELECT SUM(NVL(fecha_devolucion,1)) FROM table1 WHERE fecha_devolucion IS NULL;
Let's compare the two queries:
select count(*)
from table1
where fecha_devolucion is null;
select count(fecha_devolucion)
from table1
where fecha_devolucion is null;
I think you misunderstand the count() function. This function counts the number of non-NULL values in its argument list. With a constant or *, it counts all rows.
So, the first counts all the matching rows. The second counts all the non-NULL values of fecha_devolucion. But there are no such values because of the where clause.
By the way, you can also do:
select sum(case fecha_devolucion is null then 1 else 0 end) as Nullfecha_devolucion
from table1;
