Combine rows if value is blank

Question

I'm using SQL-Server 2008. I need to combine rows with the same Name and increase counter when:

1. 1 or more Id's for the same Name is blank
2. NOT merge rows if Id is NULL!
3. NOT merge rows if have the same Name, but different Ids

Output for now:

Name    Id   Cnt
John    1     1
Peter   2     2 -- This Peter with the same Id have 2 entries so Cnt = 2
Peter   3     1 -- This is other Peter with 1 entry so Cnt = 1
Lisa    4     1
Lisa   NULL   1
David   5     1
David         1 -- here Id is blank ''
Ralph         2 -- Ralph have both rows with blank Id so Cnt = 2

Desired output:

Name    Id   Cnt
John    1     1
Peter   2     2
Peter   3     1
Lisa    4     1
Lisa   NULL   1 -- null still here
David   5     2 -- merged with blank '' so Cnt = 2
Ralph         2 -- merged both blanks '' so Cnt = 2

SQL-Query:

This is sample query what I'm using for now:

SELECT Name, 
       Id, 
       COUNT(Id) AS Cnt
FROM Employees                       
WHERE Condition = 1                 
GROUP BY Name, Id

What I have tried:

Added aggregate MAX to Id in SELECT clause and grouped by Name only, but in this case merged rows with NULL values and with the same names with different Id's what is wrong for me.

SELECT Name, 
       MAX(Id), -- added aggregate
       COUNT(Id) AS Cnt
FROM Employees                       
WHERE Condition = 1                 
GROUP BY Name -- grouped by Name only

Have you any ideas? If anything is not clear about problem - ask me, I will provide more details.

UPDATE:

DDL

CREATE TABLE Employees
(
  Name NVARCHAR(40),
  Id NVARCHAR(40)
);

DML

INSERT INTO Employees VALUES
 ('John' ,   '1')
,('Peter',   '2')
,('Peter',   '2')
,('Peter',   '3')
,('Lisa' ,   '4')
,('Lisa' ,  NULL)
,('David',   '5')
,('David',   '')
,('Ralph',   '')
,('Ralph',   '')

DEMO: SQL FIDDLE

Answer 1

Edit

DECLARE @Data table (Name varchar(10), Id varchar(10)) -- Id must be varchar for blank value
INSERT @Data VALUES
('John', '1'),
('Peter', '2'),('Peter', '2'), 
('Peter', '3'),--('Peter', ''), --For test
('Lisa', '4'),
('Lisa', NULL),
('David', '5'),
('David', ''),
('Ralph', ''), ('Ralph', '')

---

SELECT 
    Name, 
    Id, 
    COUNT(*) + ISNULL(
        (SELECT COUNT(*) FROM @data WHERE Name = d.Name AND Id = '' AND d.Id <> '')
    , 0) AS Cnt 
FROM @data d 
WHERE 
    Id IS NULL 
    OR Id <> '' 
    OR NOT EXISTS(SELECT * FROM @data WHERE Name = d.Name AND Id <> '')
GROUP BY Name, Id

Answer 2

You can use CASE statement inside your SELECT. It allows you to set Id = [some value] for employees where it is blank. Query can be something like this:

SELECT E.Name, 
             CASE 
                  WHEN E.Id = ''
                     THEN 
                        (Select Employees.Id from Employees where Employees.Id <> '' and E.Name = Employees.Name)
                  ELSE E.Id 
             END as Idx, 
       COUNT(Id) AS Cnt
FROM Employees as E                     
WHERE Condition = 1                 
GROUP BY Name, Idx

Answer 3

A version with window functions:

SELECT Name,ID, Cnt from
( select *, sum(1-AmtBlank) over (partition by Name, ID) + sum(case id when 0 then 1 else 0 end) over (partition by Name) Cnt,
       rank()  over (partition by Name order by AmtBlank  ) rnk,
       row_number() over (partition by Name, ID order by AmtBlank) rnr
    FROM (select * , case id when '' then 1 else 0 end AmtBlank from Employees /*WHERE Condition = 1*/ ) e
) c where  rnr=1 and rnk = 1

This uses case id when '' then 1 else 0 end AmtBlank to keep an amount for the blank amounts per row (making the amount for non blanks 1-AmtBlank) and 2 window functions, one with id for a count per name and id (sum(1-AmtBlank) over (partition by Name, ID)) and a count for all blanks in a name section (sum(case id when 0 then 1 else 0 end) over (partition by Name)) The row_number is used to subsequently fetch only the first rows of a group and rank is used to only include the blank records when there are no records with an id.