Need help counting different values in the same table in SQL

Question

I'm new to SQL and I'm having trouble with a count query. I want to count the number of results that return a value and also return a second count if the value is null.

Here is what I have so far. If anyone can help I would appreciate it. Thanks.

Select
    Sum(Case When Column name = '!NULL' Then 1 Else 0 End) as [Policy ID],
    Sum(Case When Column name = 'NULL' Then 1 Else 0 End) as [No Policy Id]
    --Count(*) as [Total]
From 
    table.name
Where 
    columnname >= '2016-01-01'

Answer 1

Use IS NULL and IS NOT NULL instead of checking nulls with equality:

SELECT
    SUM(CASE WHEN Column_name IS NOT NULL THEN 1 ELSE 0 END) AS [Policy ID],
    SUM(CASE WHEN Column_name IS NULL     THEN 1 ELSE 0 END) AS [No Policy Id]
--COUNT(*) AS [Total]
FROM table.name
WHERE columnname >= '2016-01-01'

In SQL the value NULL means "unknown" and hence comparing a column value against it using = also yields an unknown result. Instead, use IS NULL or IS NOT NULL.

Answer 2

@TimBiegelesien's answer is right the only thing I would add/suggest would be if ColumnName contains an Empty string ('') and you want to count it as NULL you could do something like this:

Select
Sum(Case When LENGTH(ColumnName) > 0 Then 1 Else 0 End) as [Policy ID],
Sum(Case When LENGTH(ColumnName) < 1 Then 1 Else 0 End) as [No Policy Id]
--Count(*) as [Total]
From table.name
Where columnname >= '2016-01-01'

Note in some rdbms LENGTH is actually LEN

Select
Sum(Case When LEN(ColumnName) > 0 Then 1 Else 0 End) as [Policy ID],
Sum(Case When LEN(ColumnName) < 1 Then 1 Else 0 End) as [No Policy Id]
--Count(*) as [Total]
From table.name
Where columnname >= '2016-01-01'

This will still work even if the datatype is numeric (int, bigint, etc.)