CODEWITHSUNDEEP
sqloracle-database
Alpha2k
Alpha2k

Reputation: 2241

Oracle SQL Developer - Count function

output

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?

Upvotes: 0

Views: 12066

Answers (3)

likhith
likhith

Reputation: 44

This is the another way how you can get the count :

SELECT SUM(NVL(fecha_devolucion,1)) FROM table1 WHERE fecha_devolucion IS NULL;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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;

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48131

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.

Upvotes: 5

Related Questions