SELECT COUNT returns 0

Question

There is table Departments

CREATE TABLE Departments
(
    id int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    name  nvarchar(100) NOT NULL,
    parentDepId int
);

I need to count the rows where parentDepId = NULL, but my query returns zero every time.

SELECT COUNT(id) as DepartmentsCount
from Departments
WHERE parentDepId = NULL;

What's wrong with it?

change the where clause to WHERE parentDepId is NULL – NMK Sep 01 '15 at 12:27 — NMK, Sep 01 '15 at 12:27

score 6 · Accepted Answer · answered Sep 01 '15 at 12:27

6

You need to use IS NULL.

SELECT COUNT(id) AS DepartmentsCount FROM Departments 
 WHERE parentDepId IS NULL

That is the only way to compare to NULL (other operators do not work).

answered Sep 01 '15 at 12:27

Thilo

It wont work id will exclude null values even though you use is null in where – koushik veldanda Sep 01 '15 at 12:29
`id` is the primary key and cannot be null – Thilo Sep 01 '15 at 12:30
ok sorry I thougt it is parentDepId thanks.. – koushik veldanda Sep 01 '15 at 12:31

score 1 · Answer 2 · answered Sep 01 '15 at 12:27

1

SELECT COUNT(Isnull(id,1)) AS DepartmentsCount
 FROM Departments 
 WHERE parentDepId IS NULL

answered Sep 01 '15 at 12:27

koushik veldanda

1

ISNULL is product specific, and no dbms is specified... – jarlh Sep 01 '15 at 12:36
Also, `id` is the primary key, and cannot be null anyway. – Thilo Sep 01 '15 at 23:34
yes I agree with you guys, no need of Isnull function here only if it is primary key – koushik veldanda Sep 02 '15 at 05:18

score 1 · Answer 3 · answered Sep 01 '15 at 12:28

1

I use syntax where Column is null. Comparison with equals sign seems wrong

answered Sep 01 '15 at 12:28

Jacek Cz

3 Answers3