0

I'm still learning the basics of SQL, and in my class we're using some preset tables with employee names, numbers, jobs, managers, etc. to run different queries.

The query SELECT MGR FROM EMP; returns the following results:

MGR
-
7839
7839
7839
7698
7698
7698
7698
7698
7566
7902
7566
7788
7782

There are 14 records total, 13 with a number (employee number of manager) and 1 that is NULL (company president, therefore no manager).

The query

SELECT COUNT(MGR) FROM EMP
WHERE MGR IS NOT NULL;

Returns a value of 13, which is correct.

The query

SELECT COUNT(MGR) FROM EMP
WHERE MGR IS NULL;

returns a value of 0, which is not correct. It should be 1 because there is a single row where the value of MGR is NULL. I checked the SQL Script we used to create and populate the tables and the value as entered is NULL. Can anyone explain why this is returning a 0 for the count of NULL values?

Wes Hampton
  • 69
  • 6

2 Answers2

2

count(expression) returns the number of rows where expression is not null. If you want the number of rows where mgr is null, then use:

select count(*) from emp
where  mgr is null;

which returns 1.

Oracle SQL Reference: count function

William Robertson
  • 15,273
  • 4
  • 38
  • 44
0

You have to use the count (*)

Your select should look like this:

SELECT COUNT(*) FROM EMP WHERE MGR IS NULL;

EdwardKirk
  • 425
  • 1
  • 7
  • 16