SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE x (
  id         INT,
  location   INT
);


INSERT INTO x VALUES
  (1, 1),
  (2, NULL),
  (3, 2)
;


3 rows affected

CREATE VIEW vwNonNull WITH SCHEMABINDING AS 
SELECT Id, Location, COUNT_BIG(*) as Count
FROM dbo.X
WHERE Location IS NOT NULL
GROUP BY Id, Location


CREATE VIEW vwNull WITH SCHEMABINDING AS 
SELECT Id, Location, COUNT_BIG(*) as Count
FROM dbo.X
WHERE Location IS NULL
GROUP BY Id, Location
	CREATE UNIQUE CLUSTERED INDEX CIX_X
	ON vwNonNull(id)
	
	
	CREATE UNIQUE CLUSTERED INDEX CIX_X
	ON vwNull(id)
CREATE VIEW vw AS
WITH CTE AS (
    SELECT *, Count as LocationCount
    FROM vwNonNull
    UNION ALL
    SELECT *, 0 as LocationCount
    FROM vwNull
)

SELECT 
      Id
      ,AVG(Location) as Location
      ,SUM(Count) as Count
      ,SUM(LocationCount) as LocationCount
FROM CTE
GROUP BY Id
INSERT INTO x VALUES (1,2) --this should bomb



Msg 2601 Level 14 State 1 Line 1
Cannot insert duplicate key row in object 'dbo.vwNonNull' with unique index 'CIX_X'. The duplicate key value is (1).
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

INSERT INTO x VALUES (1,NULL) --this should NOT bomb
1 rows affected

SELECT *
FROM vw
ORDER BY Id, Location

/*
This view should look like this after the insertion from the null:

Id	Location	Count	LocationCount
1	1	        2	1
2	NULL    	1	0
3	2	        1	1

*/


Id
Location
Count
LocationCount


1
1
2
1

2
null
1
0

3
2
1
1


Warning: Null value is eliminated by an aggregate or other SET operation.