I can’t get rows have NULL on all rows for column zfeaturekey based on zplid and code type?

I work with SQL Server 2012. I have an issue I can’t get rows from table #gen when it has Null only on all rows on zfeaturekey field based on zplid and codetypeid.

I need to get rows that have NULL only on all rows ON zfeaturekey, but must be same codetypeid and same zplid .

CREATE TABLE #gen
(
    CodeTypeId int,
    Zplid  int,
    Zfeaturekey nvarchar(50)
)

INSERT INTO #gen 
VALUES 
  (854838, 25820, NULL),
  (849774, 25820, NULL),
  (849774, 25820, NULL),
  (849774, 25820, NULL),
  (849774, 25820, NULL),
  (987431, 26777, NULL),
  (987431, 26777, 1502280005),
  (987431, 26777, 1502290001)

Output:

 CodeTypeId       Zplid    Zfeaturekey
 -------------------------------------
 854838            25820    NULL
 849774            25820    NULL
 849774            25820    NULL
 849774            25820    NULL
 849774            25820    NULL

This is the result I need:

 CodeTypeId      Zplid      COUNT
 ---------------------------------
 854838          25820        1
 849774          25820        4

I will not take zplid = 26777 because it has Null and values as 1502280005 on another rows.

I need to select where feature is NULL on all rows by zplid and codetypeid .

Answer

You can use the group by and having.

Select codetypeid, zplid, count(1) as cnt
From t
Group by codetypeid, zplid
Having count(zfeaturekey) = 0